Average Resolution Time

Hi Manage Engine Community!

I found this very helpful report on the KB section of the PitStop which shows the Average Resolution Time based on Technicians and there was separate one for Group too!

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