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?
Related Articles
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) ...Query to show first assigned and re-assigned ticket irrespective of the created date ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request ...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 ...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 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", ...