Query to show Approver details, sent, acted on time along with time taken to approve. ( PGSQL )

Query to show Approver details, sent, acted on time along with time taken to approve. ( 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)) "Response time per status change",
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
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) ORDER BY 1

Same query with Approver name and limited columns:

SELECT wo.WORKORDERID "Request ID",
      rtd.name "Request Type",
       aau.FIRST_NAME "Requester",
       wo.TITLE "Subject",
      cd.categoryname "Category",
       ti.FIRST_NAME "Technician",
      appsau.first_name "Approver Name", 
      LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Time",
       ApprovalStatusDefinition.STATUSNAME "Approval Status"
 FROM WorkOrder wo
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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN Requesttypedefinition rtd ON wos.requesttypeid=rtd.requesttypeid
LEFT JOIN categorydefinition cd on wos.categoryid=cd.categoryid
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 appsau on appsau.user_id=ApprovalDetails.approverid
WHERE (wo.ISPARENT='1')
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth> ORDER BY 1

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                        Related Products