Query to find Time elapsed by each status in requests( MSSQL & PGSQL)

Query to find Time elapsed by each status in requests( MSSQL & PGSQL)


Tested in Build PGSQL (14300) or MSSQL (14306)


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>

PGSQL

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", (wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60  "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')


                  New to ADSelfService Plus?