Time elapsed by each status in requests. MSSQL

Time elapsed by each status in requests. MSSQL

SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", wotodesc.FULLDESCRIPTION AS "Description", ad.ORG_NAME AS "Account", serdef.NAME AS "Service Category",reqtemp.templatename "Template Name",std1.STATUSNAME "From Status", std2.STATUSNAME "To Status",cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Elapsed" FROM WorkOrder wo 
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID 
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID 
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 AccountSiteMapping asm ON wo.siteid=asm.siteid 
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id 
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_STATUS_INFO wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN statusdefinition std1 ON wsi.statusid=std1.statusid
LEFT JOIN statusdefinition std2 ON wsi.nextstatusid=std2.statusid
WHERE (wo.ISPARENT='1')

Status change with few columns along with changed time:

SELECT wo.WORKORDERID AS "Request ID", ti.FIRST_NAME AS "Technician", ad.ORG_NAME AS "Account", std1.STATUSNAME "From Status", std2.STATUSNAME "To Status",cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Spent in previous status (HH:MM)", longtodate(wsi.ENDTIME) as "Status Changed Time" FROM WorkOrder wo 
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID 
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID 
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 AccountSiteMapping asm ON wo.siteid=asm.siteid 
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id 
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_STATUS_INFO wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN statusdefinition std1 ON wsi.statusid=std1.statusid 
LEFT JOIN statusdefinition std2 ON wsi.nextstatusid=std2.statusid
WHERE((wo.ISPARENT = '1' and wsi.NEXTSTATUSID IS Not Null) AND (wo.createdtime/1000 between datediff(s,'1970-01-01 00:00','2020-01-20 00:00') and datediff(s,'1970-01-01 00:00','2021-12-31 00:00'))) ORDER BY 1,woa.ASSESSMENTID asc

Date range can be modified in the highlighted text. You can also change the date filters to specific time periods.

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>



          • Related Articles

          • Query to show Last Status Changed Time and Time spent in previous status

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician",  cd.CATEGORYNAME AS "Category",  scd.NAME AS "Subcategory",  qd.QUEUENAME AS "Group",  std.STATUSNAME "Request Status", LONGTODATE(MAX(OPERATIONTIME)) "Last ...
          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • How Time Elapsed is calculated?

            What is Time Elapsed? Time Elapsed is the calculation of the time taken for the completion of the request created with a proposed solution. How Time Elapsed is calculated? ** This calculation depends upon Sites,Operational Hours,Holidays,Request ...
          • Query to show both requests and task worklog time spent together

            MSSQL: select wo.workorderid "Request ID", aau.FIRST_NAME "Requester", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as ...
          • Query to find status change, its time and comments.

            Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", ...