Query to show problem time spent along with last activity on a problem

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

          • Related Articles

          • Query to show Last Status Changed Time and Time spent in previous status

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician",  cd.CATEGORYNAME AS "Category",  scd.NAME AS "Subcategory",  qd.QUEUENAME AS "Group",  std.STATUSNAME "Request Status", LONGTODATE(MAX(OPERATIONTIME)) "Last ...
          • Query to show Total Onhold time_PGSQL

            PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", ...
          • 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 show total time taken to resolve and total time onhold _ MSSQL

            MSSQL: 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", "urgdef"."NAME" AS "Urgency", ...
          • Query to show the last worklog added in a ticket

            PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...