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 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 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 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", ...