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?
- 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
- 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?
Related Articles
Query to show Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) 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", ...Query to find status change, its time and comments (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request ...Query to get total worklog time spent on a request. (PGSQL)
Tested in build PGSQL (14300) Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. ...Query to show both requests and task worklog time spent together ( MSSQL )
Tested in MSSQL build (14306) Timespent for both tasks and requests 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 ...Time elapsed analysis
This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...