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

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 Status Changed Time",
(wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60  "Time spent in previous status (HH:MM)",
ApprovalDetails.EMAIL "Approver Email",
asd.stagename "Stage",
ApprovalStatusDefinition.STATUSNAME "Approval Status",
LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent Time",
LONGTODATE(ApprovalDetails.ACTION_DATE) "Last Acted On the Approval (Approval time)",
TO_CHAR(((ApprovalDetails.ACTION_DATE-ApprovalStage.SENT_DATE)/1000 || ' second')::interval, 'HH24:MI:SS') "Approval duration"
 FROM WorkOrder wo
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
LEFT JOIN approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN approvalstagedefinition asd ON asd.STAGEID=ApprovalDetails.STAGEID
LEFT JOIN aaauser createduser on createduser.user_id=wo.createdbyid
left join workorder_queue ON workorder_queue.workorderid=wo.workorderid 
left join queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
LEFT JOIN ServiceCatalog_Fields scf on scf.workorderid=wo.workorderid
LEFT JOIN requesttemplate_list reqlist on reqlist.templateid=wo.templateid
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_STATUS_INFO wsi on woa.assessmentid=wsi.assessmentid
WHERE wohd.COLUMNNAME IN ('STATUSID') AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
GROUP BY wo.workorderid, wo.TITLE, ti.FIRST_NAME,  cd.CATEGORYNAME ,  scd.NAME ,  qd.QUEUENAME,  std.STATUSNAME, ApprovalDetails.EMAIL, 
asd.stagename , ApprovalStatusDefinition.STATUSNAME, LONGTODATE(ApprovalStage.SENT_DATE), LONGTODATE(ApprovalDetails.ACTION_DATE), wsi.timespent ORDER BY 1, wsi.TIMESPENT ASC


          • Related Articles

          • 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 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 ...
          • 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 ...
          • Time elapsed by each status in requests. MSSQL

            SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS ...
          • 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", ...