Query to get the Request First Assigned time

Query to get the Request First Assigned time


Use case:
First Assigned Time is the number of minutes, hours, or days between when a requester submits a request and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state. 

Working on Build: 14500
Database:   PGSQL

SELECT wo.WORKORDERID "Request ID",
       Longtodate(max(wo.CREATEDTIME)) "Created Time",
       longtodate(max(woh.OPERATIONTIME)) "Assigned time",
       max(aau.FIRST_NAME) "Requester",
       max(wo.TITLE) "Subject",
       max(qd.QUEUENAME) "Group",
       min(prev.first_name) "Technician",
       max(ti.FIRST_NAME) "Current Technician",
       max(pd.PRIORITYNAME) "Priority",
       max(cd.CATEGORYNAME) "Category",
       max(scd.NAME) "Subcategory",
       max(std.STATUSNAME) "Request 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 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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN workorderhistory woh ON wo.workorderid=woh.workorderid
LEFT JOIN workorderhistorydiff wohd ON woh.historyid=wohd.historyid
LEFT JOIN aaauser prev ON cast(cast(wohd.current_value AS varchar) AS int)=prev.user_id
WHERE (wo.ISPARENT='1')
  AND wohd.columnname='ownerid'
  AND woh.historyid=
    (SELECT min(workorderhistory.historyid) FROM workorderhistory
     LEFT JOIN workorderhistorydiff ON workorderhistory.historyid=workorderhistorydiff.historyid
     WHERE workorderid=wo.workorderid
       AND workorderhistorydiff.COLUMNNAME ='ownerid' )
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth>
GROUP BY wo.WORKORDERID ORDER BY 1

                  New to ADSelfService Plus?