Query to show the time taken between created time and first technician assigned time (PGSQL)

Query to show the time taken between created time and first technician assigned time (PGSQL)


Tested in Build PGSQL (14300) or MSSQL (14306)

Go to Reports-New Query Report and execute this query.

PGSQL:

SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
LONGTODATE(wo.createdtime) "Created Time",
LONGTODATE(wo.COMPLETEDTIME) "Completed Time",
LONGTODATE(MIN(woh.OPERATIONTIME)) "Assigned Time",
lvd.LEVELNAME AS "Level",
pd.PRIORITYNAME AS "Priority",
MAX(wo.TITLE) "Subject",
TO_CHAR(((min(woh.OPERATIONTIME)-min(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Unassigned Time",
cast(sum(wo.TIMESPENTONREQ/1000 ) * '1 second'::interval as varchar) "Time Elapsed",
MAX(ti.FIRST_NAME) "Currently Assigned Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
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 WorkOrderHistory woh ON wo.WORKORDERID=woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID=wohd.HISTORYID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
WHERE (wo.ISPARENT='1') AND wohd.COLUMNNAME='AssignedTime' AND wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-09-01 00:00:00') * 1000 AS BIGINT)  AND  
wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-09-30 00:00:00') * 1000 AS BIGINT) group by wo.WORKORDERID,aau.FIRST_NAME,wo.CREATEDTIME,wo.COMPLETEDTIME,wo.TIMESPENTONREQ,lvd.LEVELNAME,pd.PRIORITYNAME




                  New to ADSelfService Plus?