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 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 ...Query to show total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...