Time spent reporting in all modules
Hi, In the below query, the time spent obtained is in decimal format. It´s possible to change it so that it appears in HH: MM: SS format?
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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid WHERE tk.MODULE IN ('project', 'milestone') AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
where module='general' AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 6
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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid WHERE tk.MODULE IN ('project', 'milestone') AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> 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
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid
left join queuedefinition q on q.queueid = qt.queueid
where module='general' AND ct.CREATEDTIME >= <from_thisyear> AND ct.CREATEDTIME <= <to_thisyear> GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 6