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?
Related Articles
Query to show Problem details, timespent and its associated request IDs (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...Time Spent on each module
This Report helps us to know the time spent by technicians on each module. The Time spent report provides a step-by-step record by which time-related data can be traced to their source and provides a complete history of all Technician activities ...Request Problem association.
This Report is used to find the request, problem association. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT wo.WORKORDERID ...Query to show problem time spent along with last activity on a problem (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...Query report to show Problem fields along with last added notes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", ...