Query to show unassigned time of a request and when its first assigned

Query to show unassigned time of a request and when its first assigned

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)