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
How to reset the IDs of Request, Problem and Change ?
To reset the ID's of the respective module follow the below steps: 1) Connect to the Database - Refer this KB to Connect to your database. 2) Use the update query mentioned below for resetting the ID's : Note: We highly recommend you to take a ...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 ...Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent) (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) This report contains the time analysis for each status/group/technician changes in a request, kindly execute this under Reports->New Query Report. Status Changes: SELECT wo.WORKORDERID AS "Request ID", ...