Completed Tasks Report
Hi,
I am looking for a report that will provide us with a listing of all tasks closed within the last month. Our database is MSSQL, and please include the following columns:
Task Owner (sort by this field)
Task Created Date
Task Closed Date
Task Number
Task Title
Task Description
Task Comments
Request Number
I have tried to create this report myself, but was unable to get everything working...this code below has many of the fields I am looking for though.
SELECT wo.WORKORDERID 'Request ID',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time',aau.FIRST_NAME 'Requester',cd.CATEGORYNAME 'Category',scd.NAME 'Sub Category',std.STATUSNAME 'Request Status',ti.FIRST_NAME 'Technician', qd.QUEUENAME "Group" , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.DUEBYTIME/1000),'1970-01-01 00:00:00') 'Due By Time',StatusDefinition.STATUSNAME 'Task Status',TaskDetailsCreater.FIRST_NAME 'Task Created By',TaskDetailsOwner.FIRST_NAME 'Task Owner',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.CREATEDDATE/1000),'1970-01-01 00:00:00') 'Task Created Date',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.SCHEDULEDSTARTTIME/1000),'1970-01-01 00:00:00') 'Task Schedule Start Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.SCHEDULEDENDTIME/1000),'1970-01-01 00:00:00') 'Task Schedule End Time',TaskDetails.TITLE,TaskDetails.DESCRIPTION 'Task Description',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.ACTUALSTARTTIME/1000),'1970-01-01 00:00:00') 'Task Actual Start Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.ACTUALENDTIME/1000),'1970-01-01 00:00:00') 'Task Actual End Time',TaskDetails.COMMENTS 'Comments' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrderToTaskDetails ON wo.WORKORDERID=WorkOrderToTaskDetails.WORKORDERID LEFT JOIN TaskDetails ON WorkOrderToTaskDetails.TASKID=TaskDetails.TASKID LEFT JOIN StatusDefinition ON TaskDetails.STATUSID=StatusDefinition.STATUSID LEFT JOIN AaaUser TaskDetailsCreater ON TaskDetails.CREATEDBY=TaskDetailsCreater.USER_ID LEFT JOIN AaaUser TaskDetailsOwner ON TaskDetails.OWNERID=TaskDetailsOwner.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (wot.THD_WOID=wot.WORKORDERID) ORDER BY 1
I am looking for a report that will provide us with a listing of all tasks closed within the last month. Our database is MSSQL, and please include the following columns:
Task Owner (sort by this field)
Task Created Date
Task Closed Date
Task Number
Task Title
Task Description
Task Comments
Request Number
I have tried to create this report myself, but was unable to get everything working...this code below has many of the fields I am looking for though.
SELECT wo.WORKORDERID 'Request ID',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time',aau.FIRST_NAME 'Requester',cd.CATEGORYNAME 'Category',scd.NAME 'Sub Category',std.STATUSNAME 'Request Status',ti.FIRST_NAME 'Technician', qd.QUEUENAME "Group" , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.DUEBYTIME/1000),'1970-01-01 00:00:00') 'Due By Time',StatusDefinition.STATUSNAME 'Task Status',TaskDetailsCreater.FIRST_NAME 'Task Created By',TaskDetailsOwner.FIRST_NAME 'Task Owner',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.CREATEDDATE/1000),'1970-01-01 00:00:00') 'Task Created Date',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.SCHEDULEDSTARTTIME/1000),'1970-01-01 00:00:00') 'Task Schedule Start Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.SCHEDULEDENDTIME/1000),'1970-01-01 00:00:00') 'Task Schedule End Time',TaskDetails.TITLE,TaskDetails.DESCRIPTION 'Task Description',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.ACTUALSTARTTIME/1000),'1970-01-01 00:00:00') 'Task Actual Start Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (TaskDetails.ACTUALENDTIME/1000),'1970-01-01 00:00:00') 'Task Actual End Time',TaskDetails.COMMENTS 'Comments' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrderToTaskDetails ON wo.WORKORDERID=WorkOrderToTaskDetails.WORKORDERID LEFT JOIN TaskDetails ON WorkOrderToTaskDetails.TASKID=TaskDetails.TASKID LEFT JOIN StatusDefinition ON TaskDetails.STATUSID=StatusDefinition.STATUSID LEFT JOIN AaaUser TaskDetailsCreater ON TaskDetails.CREATEDBY=TaskDetailsCreater.USER_ID LEFT JOIN AaaUser TaskDetailsOwner ON TaskDetails.OWNERID=TaskDetailsOwner.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (wot.THD_WOID=wot.WORKORDERID) ORDER BY 1