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 '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60)
as varchar(20))+ 'Secs' "Time Spent", "catadef"."CATEGORYNAME" AS "Category", "ownaaa"."FIRST_NAME" AS "Technician", "statdef"."STATUSNAME" AS "Status", "priodef"."PRIORITYNAME" AS "Priority", wo.WORKORDERID AS "Associated Request IDs", LONGTODATE(ph.operationtime) "Date of last activity" FROM "Problem" "prob"
LEFT JOIN "SDUser" "orgsd" ON "prob"."ORIGINATORID"="orgsd"."USERID"
LEFT JOIN "AaaUser" "orgaaa" ON "orgsd"."USERID"="orgaaa"."USER_ID"
LEFT JOIN "SDUser" "ownsd" ON "prob"."OWNERID"="ownsd"."USERID"
LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID"
LEFT JOIN "StatusDefinition" "statdef" ON "prob"."STATUSID"="statdef"."STATUSID"
LEFT JOIN "PriorityDefinition" "priodef" ON "prob"."PRIORITYID"="priodef"."PRIORITYID"
LEFT JOIN "CategoryDefinition" "catadef" ON "prob"."CATEGORYID"="catadef"."CATEGORYID"
LEFT JOIN ProblemToCharge pbtoc ON pbtoc.PROBLEMID=prob.PROBLEMID
LEFT JOIN ChargesTable ct ON ct.CHARGEID=pbtoc.CHARGEID
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 problemtoincidentmapping pim ON prob.problemid=pim.problemid
LEFT JOIN workorder wo ON pim.workorderid=wo.workorderid
LEFT JOIN Problemhistory PH ON ph.problemid=prob.problemid
LEFT JOIN Problemhistorydiff phf ON ph.historyid=phf.historyid
WHERE ph.historyid=(select max(problemhistory.historyid) from problemhistory left join problemhistorydiff on problemhistory.historyid=problemhistorydiff.historyid where problemhistory.problemid=prob.problemid )
GROUP BY statdef.STATUSNAME, prob.PROBLEMID, prob.TITLE, orgaaa.FIRST_NAME, catadef.CATEGORYNAME, ownaaa.FIRST_NAME, priodef.PRIORITYNAME, wo.workorderid, PH.operationtime ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...Query to show total time spent for a ticket ( PGSQL)
Tested in PGSQL build (14300) Database: PGSQL SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS ...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 total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...