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",
LONGTODATE(MIN(woh.OPERATIONTIME)) "Assigned Time",
MAX(wo.TITLE) "Subject",
MAX(ti.FIRST_NAME) "Currently Assigned Technician",
queuedefinition.queuename "Currently Assigned Group",
convert(varchar(10),(min(woh.OPERATIONTIME)-min(wo.createdtime))/1000/3600)+':'+convert(varchar(10),((min(woh.OPERATIONTIME)-min(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),(((min(woh.OPERATIONTIME)-min(wo.createdtime)))/1000%60)) "Unassigned Time" 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 workorder_queue ON workorder_queue.workorderid=wo.workorderid
LEFT JOIN queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
WHERE (wo.ISPARENT='1') AND wohd.COLUMNNAME='AssignedTime' AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2019-08-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2021-08-31 23:59',21) group by wo.WORKORDERID,aau.FIRST_NAME,wo.CREATEDTIME,wo.COMPLETEDTIME,wo.TIMESPENTONREQ,queuedefinition.QUEUENAME
Works in SQL editor, helpful in cases of Power BI purpose:
SELECT wo.WORKORDERID 'Request ID', dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time', ti.FIRST_NAME 'Technician',pd.priorityname 'Priority',wo.TITLE 'Subject',aau.FIRST_NAME 'Requester',qd.QUEUENAME 'Group', ad.org_name "Account", sdo.NAME 'Site',std.STATUSNAME 'Request Status',(select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wohi.operationtime/1000),'1970-01-01 00:00:00') from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID')) 'Operation Time', convert(varchar,ROUND(((((select wohi.operationtime from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID'))-wo.CREATEDTIME)/1000)/3600),2)) +':'+ convert(varchar,ROUND((((((select wohi.operationtime from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID'))-wo.CREATEDTIME)/1000)/60)) % 60,2)) 'UnAssigned Time (HH:MM)',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00') 'Completed Time' from WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 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 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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE wo.ISPARENT='1' and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2019-08-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2021-08-31 23:59',21)
New to ADSelfService Plus?
Related Articles
Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...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 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 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 tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...