Timespent report across modules (Request, Problem, Change) (MSSQL)

Timespent report across modules (Request, Problem, Change) (MSSQL)


Tested in build MSSQL (14306)

SELECT ad.ORG_NAME AS "Account",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", Convert(varchar(10),CONVERT(date,(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((ct.CREATEDTIME)/1000),'1970-01-01 00:00:00')),106),103) +' '+CONVERT(varchar(15),CAST(
(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00')) AS TIME),100) "Time Spent Created Time",'Request' "Module", CAST(((ct.TIMESPENT)/3600000) AS VARCHAR(20)) +':'+
CAST((((ct.TIMESPENT) % 3600000)/ 60000) AS VARCHAR(20)) "Time Spent HH:MM", rctd.FIRST_NAME "Technician" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN workorderstates wos ON wos.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 wos.ownerid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME, ct.timespent
UNION
SELECT ad.ORG_NAME AS "Account",COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",Convert(varchar(10),CONVERT(date,(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((ct.CREATEDTIME)/1000),'1970-01-01 00:00:00')),106),103) +' '+CONVERT(varchar(15),CAST(
(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00')) AS TIME),100) "Time Spent Created Time",'Problem' "Module", CAST(((ct.TIMESPENT)/3600000) AS VARCHAR(20)) +':'+
CAST((((ct.TIMESPENT) % 3600000)/ 60000) AS VARCHAR(20)) "Time Spent HH:MM",rctd.FIRST_NAME "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 pb.ownerid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN AccountSiteMapping asm ON pb.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.timespent
UNION
SELECT ad.ORG_NAME AS "Account",COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", Convert(varchar(10),CONVERT(date,(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((ct.CREATEDTIME)/1000),'1970-01-01 00:00:00')),106),103) +' '+CONVERT(varchar(15),CAST(
(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00')) AS TIME),100) "Time Spent Created Time",'Change' "Module",CAST(((ct.TIMESPENT)/3600000) AS VARCHAR(20)) +':'+
CAST((((ct.TIMESPENT) % 3600000)/ 60000) AS VARCHAR(20)) "Time Spent HH:MM",rctd.FIRST_NAME "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 ch.technicianid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN AccountSiteMapping asm ON ch.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.timespent


Note : when combining multiple queries across the modules we cant have different column in each modules modules should have same columns which has been given.

                  New to ADSelfService Plus?