Average Resolution Time based on Group

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.


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 

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 




          • Related Articles

          • 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 ...
          • Time elapsed analysis

            This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...
          • Asset group

            SELECT max(resgrp.GROUPNAME) AS "Group Name", MAX(resource.RESOURCENAME) AS "Asset Name", MAX(state.DISPLAYSTATE) AS "Asset State", MAX(rCategory.CATEGORY) AS "Asset Category", MAX(product.COMPONENTNAME) AS "Product", ...
          • Keep track of your requests with Time Elapsed Analysis in ServiceDesk Plus.

             Time-elapsed analysis provides information on how long any request was handled by a technician and remained in a group or status.   Select any request from the Request List View and click the clock icon next to the History tab in the Request ...
          • Last scanned time

            SELECT resource.resourcename                       "Asset Name",         Max(net.ipaddress)                          "IP Address",         Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",  ...