Query to get the time spent in the “unassigned” status for each request before the request is assigned to a technician.
Time spent column format to DD:HH:MM:SS
SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Changed From",aa.first_name as "Changed To",longtodate(wot.ENDTIME) as "Changed Time",concat(floor(wot.TIMESPENT/(1000*60*60*24)),':',floor(Mod(wot.TIMESPENT, 24*60*60*1000)/(60*60*1000)),':',floor(mod(Mod(wot.TIMESPENT, 24*60*60*1000),(60*60*1000))/(60*1000)),':',floor(Mod(mod(Mod(wot.TIMESPENT, 24*60*60*1000),(60*60*1000)),(60*1000))/1000),'') 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
New to ADSelfService Plus?
Related Articles
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 get the Request First Assigned time
Use case: First Assigned Time is the number of minutes, hours, or days between when a requester submits a request and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state. Working on Build: ...Query to show unassigned time of a request and when its first assigned (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...Query to show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) ...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", ...