SQL examples for SQL Server

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Workspaces';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Projects';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Tasks';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Tasks WITH (ProjectId=''1200652735638082'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM SubTasks';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM SubTasks WITH (ProjectId=''1200652735638082'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM SubTasks  WITH(QueryBy=''task'', TaskId=''1207150838176996'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkspaceTasks';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = '--//search text in task title or description
SELECT * FROM search_tasks WITH(Text=''Hotfix'')

--//search text in all tasks from any projects listed below
SELECT * FROM search_tasks WITH(Text=''Hotfix'', ProjectsAny=''1200652735638082,1206028542305053'')

--//list all tasks from any projects listed below
SELECT * FROM search_tasks WITH(Text=''Hotfix'', ProjectsAny=''1200652735638082,1206028542305053'')

--//list all tasks due on specified date (same way you can use CreatedOn, CompletedOn ...) 
SELECT * FROM search_tasks WITH(DueOn=''2024-12-21'')

--//list all tasks with due date after specified date (same way you can use CreatedOnAfter, CompletedOnAfter ...) 
SELECT * FROM search_tasks WITH(DueOnAfter=''2024-12-21'')
SELECT * FROM search_tasks WITH(DueOnAfter=''yearstart+1d'')

--//list all tasks with due date after specified date (same way you can use CreatedOnBefore, CompletedOnBefore ...) 
SELECT * FROM search_tasks WITH(DueOnBefore=''2024-12-21'')
SELECT * FROM search_tasks WITH(DueOnBefore=''monthstart-5d'')

--//list all tasks with due date after specified datetime (same way you can use CreatedAtBefore, CompletedAtBefore ...) 
SELECT * FROM search_tasks WITH(DueAtBefore=''2024-12-21T23:59:59'')
SELECT * FROM search_tasks WITH(DueAtBefore=''now-10h'')

--//list all tasks with attachment
SELECT * FROM search_tasks WITH(HasAttachment=''true'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM download_attachment 
WITH (
   AttachmentId=''1208216218258030''
 , TargetFilePath=''c:\temp\1208216218258030_AWS-refund-request.png'' 
 , FileOverwriteMode=0 --0=AlwaysOverwrite, 1=FailIfExists, 2=SkipIfExists
)';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM download_attachments
WITH (
   ParentId=''1206673375982078'' --Id of Task , Project or ProjectBrief
 , SaveFolder=''c:\temp'' 
 , OverwriteFile=1
)';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Portfolios WITH(OwnerId=''1200442904128317'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Portfolios WITH(OwnerId=''1200442904128317'', WorkspaceId=''1200442900140748'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Goals WITH(Project=''1206673375982078'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Goals WITH(Workspace=''1200442900140748'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Goals WITH(Team=''1200442900140748'')';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Memberships WITH(ProjectId=1206673375982078)';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Memberships WITH(PortfolioId=1206673375982077)';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM TimeTrackingEntries WITH(TaskId=1207150837043133)';

EXEC (@MyQuery) AT [LS_TO_ASANA_IN_GATEWAY];