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 )

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) "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>




      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • 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 show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...

                    • 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) ...

                    • Time taken to First Response

                      First Response Time is the number of minutes, hours, or days between when a customer submits a support ticket and when a customer support representative provides an initial response. It indicates how long a customer has to wait before being helped. ...

                      Related Products