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
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?
Related Articles
Query to show ticket first assign/pick up time _ (PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME ...Query to show unassigned time of a request and when its first assigned (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time ( MSSQL )
Tested in MSSQL build (14306) MSSQL: First responded time: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) ...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: ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...