Average Resolution Time based on Technicians

This report is used for the customers that Technicians provide a consistent level of support. It can be confusing and frustrating for customers to have some Technicians resolve quickly, while other Technicians takes days to resolve the issues and address them.

To make any changes to a query, refer to the KB article below.

https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275

PGSQL

SELECT aau.first_name "Technician",
       TO_CHAR(((avg(wo.resolvedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg resolution Time" 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
WHERE wo.resolvedtime !='0'
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY aau.first_name

MSSQL

SELECT aau.first_name "Technician",
       convert(varchar(10), (avg(wo.resolvedtime)-avg(wo.createdtime))/1000/3600)+':'+convert(varchar(10), ((avg(wo.resolvedtime)-avg(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),(((avg(wo.resolvedtime)-avg(wo.createdtime)))/1000%60)) "Avg resolution Time" 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
WHERE wo.resolvedtime !='0'
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY aau.first_name