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 '+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", LONGTODATE(ph.operationtime) "Date of last activity", "priodef"."PRIORITYNAME" AS "Priority", "solres"."DESCRIPTION" AS "Solution", STUFF((SELECT ','+ cast(pim.workorderid as varchar) + char(10) FROM problemtoincidentmapping pim where prob.problemid=pim.problemid FOR XML PATH ('')), 1, 1, '') "Associated Request IDs" 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 Problemhistory PH ON ph.problemid=prob.problemid
LEFT JOIN Problemhistorydiff phf ON ph.historyid=phf.historyid
LEFT JOIN "SolutionToResolution" "probres" ON "prob"."PROBLEMID"="probres"."PROBLEMID"
LEFT JOIN "Solution" "solres" ON "probres"."SOLUTIONID"="solres"."SOLUTIONID"
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, PH.operationtime, solres.description ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show problem time spent along with last activity on a problem
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 Time spent in each status - Time Analysis
MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...Query to get total worklog time spent on a request.
Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. Query: SELECT aau.FIRST_NAME AS ...Query to show total time spent of a technician for the current month_PGSQL
Query show total time spent by technician for the current month regardless of the ticket created date. PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS ...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 ...