Worklog Report Across All Modules - With Sum
Hi all,
I am trying to create a report which will show all worklogs for this week, which includes worklogs across all modules. I also need to get the task title (rather than the parent title), and need to do both a sum per module as well as a total sum for the week.
I have used the example given at the link below as a starting point:
https://pitstop.manageengine.com/portal/community/topic/worklog-report
This is a great start, but I need to further refine it as above (task titles and sum of time spent per module and total at the end across all worklogs). Is there a way this can be achieved?
This is what has been used so far - thanks in advance for any help:
SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", 'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith'
GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME
UNION
SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME
UNION
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", 'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID
LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID
LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME
UNION
SELECT tpr.PROJECTID "Module ID",'Project' "Module" , MAX(pr.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600
"Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE tk.MODULE IN ('project', 'milestone') AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY tpr.PROJECTID,rctd.FIRST_NAME
UNION
SELECT tk.TASKID "Module ID", 'General' "Module" ,MAX(tk.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
where module='general' AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 2
I am trying to create a report which will show all worklogs for this week, which includes worklogs across all modules. I also need to get the task title (rather than the parent title), and need to do both a sum per module as well as a total sum for the week.
I have used the example given at the link below as a starting point:
https://pitstop.manageengine.com/portal/community/topic/worklog-report
This is a great start, but I need to further refine it as above (task titles and sum of time spent per module and total at the end across all worklogs). Is there a way this can be achieved?
This is what has been used so far - thanks in advance for any help:
SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", 'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith'
GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME
UNION
SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME
UNION
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", 'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID
LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID
LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME
UNION
SELECT tpr.PROJECTID "Module ID",'Project' "Module" , MAX(pr.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600
"Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE tk.MODULE IN ('project', 'milestone') AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY tpr.PROJECTID,rctd.FIRST_NAME
UNION
SELECT tk.TASKID "Module ID", 'General' "Module" ,MAX(tk.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
where module='general' AND ct.CREATEDTIME >= <from_thisweek> AND ct.CREATEDTIME <= <to_thisweek> AND rctd.FIRST_NAME='John Smith' GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 2