Timespent report across modules (Request, Problem, Change)
DS: MSSQL
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 TimeSpent for all the modules together for 10.6 and above.
PGSQL: SELECT ad.ORG_NAME AS "Account",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Request' "Module", sum((ct.TIMESPENT)/1000) "Time Spent", rctd.FIRST_NAME ...How to reset the IDs of Request, Problem and Change ?
Note: We highly recommend you to take a trimmed backup of the application before executing the query. Below is the compatible query for 10.6 version and above : - Refer this KB to Connect to your database. Request: update seqgenstate set ...Query to show TimeSpent for all the modules together
PGSQL: Kindly SELECT ad.ORG_NAME AS "Account",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Request' "Module", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') as ...Query to show Problem details, timespent and its associated request IDs
MSSQL: 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 (PGSQL)
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", probdesc.FULL_DESCRIPTION AS "Description", ...