Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent)
This report contains the time analysis for each status/group/technician changes in a request.
For builds lower than 11122.
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
)
ORDER BY
1,
woas.ASSESSMENTID asc
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
)
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
)
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
)
ORDER BY
1,
woas.ASSESSMENTID asc
For build 11122 and above.
PGSQL
Status Changes:
SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Status Changed From",sinsd.STATUSNAME as "Status Changed To",longtodate(si.ENDTIME) as "Status Changed Time",
(concat( floor((si.TIMESPENT/1000)/60/60/24) , ' : ' , floor((si.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((si.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(si.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Status in Days:HH:MM:SS" 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
)
ORDER BY
1,
woas.ASSESSMENTID asc
Group Changes:
SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Group Changed From",qq.queuename as "Group Changed To",longtodate(gi.ENDTIME) as "Group Changed Time", (concat( floor((gi.TIMESPENT/1000)/60/60/24) , ' : ' , floor((gi.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((gi.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(gi.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Group in Days:HH:MM:SS" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_group_Info gi ON woas.ASSESSMENTID = gi.ASSESSMENTID
LEFT JOIN queuedefinition q ON gi.GROUPID = q.QUEUEID
LEFT JOIN queuedefinition qq ON gi.NEXTGROUPID = qq.QUEUEID
WHERE
(
wo.ISPARENT = '1'
and gi.NEXTGROUPID IS Not Null
)
ORDER BY
1,
woas.ASSESSMENTID asc
Technician Changes:
SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Technician Changed From",aa.first_name as "Technician Changed To",longtodate(ti.ENDTIME) as "Technician Changed Time",(concat( floor((ti.TIMESPENT/1000)/60/60/24) , ' : ' , floor((ti.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((ti.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(ti.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Technician in Days:HH:MM:SS" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Tech_Info ti ON woas.ASSESSMENTID = ti.ASSESSMENTID
LEFT JOIN AAAUser a ON ti.TECHNICIANID = a.USER_ID
LEFT JOIN AAAUser aa ON ti.NEXTTECHNICIANID = aa.USER_ID
WHERE
(
wo.ISPARENT = '1'
and ti.NEXTTECHNICIANID IS Not Null
)
ORDER BY
1,
woas.ASSESSMENTID asc
MSSQL
Status Changes:
SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Status Changed From",sinsd.STATUSNAME as "Status Changed To",longtodate(si.ENDTIME) as "Status Changed Time",
(CONVERT(varchar, (si.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((si.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Status in Days:HH:MM:SS" 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
)
ORDER BY
1,
woas.ASSESSMENTID asc
Group Changes:
SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Group Changed From",qq.queuename as "Group Changed To",longtodate(gi.ENDTIME) as "Group Changed Time",
(CONVERT(varchar, (gi.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((gi.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Group in Days:HH:MM:SS" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_group_Info gi ON woas.ASSESSMENTID = gi.ASSESSMENTID
LEFT JOIN queuedefinition q ON gi.GROUPID = q.QUEUEID
LEFT JOIN queuedefinition qq ON gi.NEXTGROUPID = qq.QUEUEID
WHERE
(
wo.ISPARENT = '1'
and gi.NEXTGROUPID IS Not Null
)
ORDER BY
1,
woas.ASSESSMENTID asc
Technician Changes:
SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Technician Changed From",aa.first_name as "Technician Changed To",longtodate(ti.ENDTIME) as "Technician Changed Time",
(CONVERT(varchar, (ti.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((ti.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Technician in Days:HH:MM:SS" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Tech_Info ti ON woas.ASSESSMENTID = ti.ASSESSMENTID
LEFT JOIN AAAUser a ON ti.TECHNICIANID = a.USER_ID
LEFT JOIN AAAUser aa ON ti.NEXTTECHNICIANID = aa.USER_ID
WHERE
(
wo.ISPARENT = '1'
and ti.NEXTTECHNICIANID IS Not Null
)
ORDER BY
1,
woas.ASSESSMENTID asc
New to ADSelfService Plus?
Related Articles
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", ...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 ...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 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", ...Timespent report across modules (Request, Problem, Change) (MSSQL)
Tested in build MSSQL (14306) SELECT ad.ORG_NAME AS "Account",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", Convert(varchar(10),CONVERT(date,(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((ct.CREATEDTIME)/1000),'1970-01-01 ...