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

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",MAX(ti.FIRST_NAME) "Currently Assigned Technician",
TO_CHAR(((min(woh.OPERATIONTIME)-min(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Unassigned Time",cast((wo.TIMESPENTONREQ/1000 * interval '1 second') as varchar) "Time Elapsed" 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