Query to show Problem details, timespent and its associated request IDs (MSSQL)

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 '+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?