Query to get Techinican Avegare response time

Query to get Techinican Avegare response time

Version : 11026
DB : MSSQL

OUTPUT :      



SELECT aau.first_name "Technician",
CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/(3600*24)) AS VARCHAR(10))+'days '+CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/3600)%24 AS VARCHAR(10))+'hours '+CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/60)%60 AS VARCHAR(10))+'min '+CAST((avg(wo.respondedtime-wo.createdtime)/(1000))%60 AS VARCHAR(10))+'sec'  "Avg response Time",
wof.UDF_CHAR2 "Business Relationship" FROM workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sdf ON wos.statusid=sdf.statusid
LEFT JOIN aaauser aau ON wos.ownerid=aau.user_id
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
WHERE wo.respondedtime !='0'  AND wo.CREATEDTIME >= <from_thisyear>
  AND wo.CREATEDTIME <= <to_thisyear>
GROUP BY aau.first_name,wof.UDF_CHAR2

Note : Custom date filter ------> where wo.respondedtime !='0' and wo.createdtime >= DATETOLONG('2022-11-30 00:00:00')  AND wo.createdtime <= DATETOLONG('2023-01-31 23:59:59')

                New to ADSelfService Plus?