Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent) (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
This report contains the time analysis for each status/group/technician changes in a request, kindly execute this under Reports->New Query Report.
Status Changes:
SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Changed From",sinsd.STATUSNAME as "Changed To",longtodate(si.ENDTIME) as "Changed Time",si.TIMESPENT as "TimeSpent in Previous Status(min)" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
WHERE
(
wo.ISPARENT = '1'
and si.NEXTSTATUSID IS Not Null
and wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
)
ORDER BY
1,
woas.ASSESSMENTID asc
Group Changes:
SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Changed From",qq.queuename as "Changed To",longtodate(woq.ENDTIME) as "Changed Time",woq.TIMESPENT as "TimeSpent by Previous Group(min)" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_group_Info woq ON woas.ASSESSMENTID = woq.ASSESSMENTID
LEFT JOIN queuedefinition q ON woq.GROUPID = q.QUEUEID
LEFT JOIN queuedefinition qq ON woq.NEXTGROUPID = qq.QUEUEID
WHERE
(
wo.ISPARENT = '1'
and woq.NEXTGROUPID IS Not Null
and wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
)
ORDER BY
1,
woas.ASSESSMENTID asc
Technician Changes:
SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Changed From",aa.first_name as "Changed To",longtodate(wot.ENDTIME) as "Changed Time",wot.TIMESPENT as "TimeSpent by Previous Technician(min)" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Tech_Info wot ON woas.ASSESSMENTID = wot.ASSESSMENTID
LEFT JOIN AAAUser a ON wot.TECHNICIANID = a.USER_ID
LEFT JOIN AAAUser aa ON wot.NEXTTECHNICIANID = aa.USER_ID
WHERE
(
wo.ISPARENT = '1'
and wot.NEXTTECHNICIANID IS Not Null
and wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
)
ORDER BY
1,
woas.ASSESSMENTID asc
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>
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", ...Time elapsed by each group in requests. PGSQL
Tested in Build PGSQL (14300) Go to Reports-New Query Report and execute the below query. SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...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 ...Query to show request history for technicians, group and status changes (MSSQL & PGSQL)
Last tested on 14500 and above too Technician select woa.workorderid "RequestID", ti1.FIRST_NAME "From technician", ti2.FIRST_NAME "To technician", longtodate(wti.timespent) "Time Spent" from workorder wo left join wo_assessment woa on ...