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

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

Tested in build PGSQL (14300)

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



                New to ADManager Plus?

                  New to ADSelfService Plus?