Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time_ MSSQL

Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time_ MSSQL

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) "Responded Date",
convert(varchar(10),((wo.RESPONDEDTIME)-(wo.createdtime))/1000/3600)+':'+convert(varchar(10),(((wo.RESPONDEDTIME)-(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),((((wo.RESPONDEDTIME)-(wo.createdtime)))/1000%60)) "Difference between Created time & Responded 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 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 WHERE (wo.ISPARENT='1') and wo.RESPONDEDTIME>'0' AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>

First technician assigned time:

SELECT wo.WORKORDERID "Request ID",
longtodate(min(wo.CREATEDTIME)) "Created Time",
longtodate(min(woh.OPERATIONTIME)) "Assigned time",
min(aau.FIRST_NAME) "Requester",
min(wo.TITLE) "Subject",
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))  "Time to Assign" 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  
WHERE (wo.ISPARENT='1') and wohd.COLUMNNAME ='ownerid' AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> 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' ) group by wo.WORKORDERID

First status set time:

SELECT wo.WORKORDERID "Request ID",
longtodate(min(wo.CREATEDTIME)) "Created Time",
longtodate(min(woh.OPERATIONTIME)) "status set time",
min(aau.FIRST_NAME) "Requester",
min(wo.TITLE) "Subject",
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))  "Time to set 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  
WHERE (wo.ISPARENT='1') and wohd.COLUMNNAME ='statusid' and wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> AND
  woh.historyid=(select min(workorderhistory.historyid) from workorderhistory left join workorderhistorydiff  on workorderhistory.historyid=workorderhistorydiff.historyid  where workorderid=wo.workorderid and workorderhistorydiff.COLUMNNAME ='statusid' ) group by wo.WORKORDERID

First group assigned time:

SELECT wo.WORKORDERID "Request ID",
longtodate(min(wo.CREATEDTIME)) "Created Time",
longtodate(min(woh.OPERATIONTIME)) "group set time",
min(aau.FIRST_NAME) "Requester",
min(wo.TITLE) "Subject",
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))  "Time to set group" 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  
WHERE (wo.ISPARENT='1') and wohd.COLUMNNAME ='QUEUEID' and  wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> AND
  woh.historyid=(select min(workorderhistory.historyid) from workorderhistory left join workorderhistorydiff  on workorderhistory.historyid=workorderhistorydiff.historyid  where workorderid=wo.workorderid and workorderhistorydiff.COLUMNNAME ='QUEUEID' ) group by wo.WORKORDERID

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>