Average Resolution Time based on Group

This report is used for the customers that support groups provide a consistent level of support. It can be confusing and frustrating for customers to have some support groups resolve quickly, while other support groups 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 qd.queuename "Group",
       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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE wo.resolvedtime !='0'
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY qd.queuename

MSSQL

SELECT qd.queuename "Group",
       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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE wo.resolvedtime !='0'
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY qd.queuename