Average Resolution Time based on Technicians

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.


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

 

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






      New to ADSelfService Plus?

      Start your free trial

        Resources

            • Related Articles

            • 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 ...

            • Dynamic checklist based on field values

              Requirement: Based on the answers provided for resource questions / request additional fields, dynamically a checklist has to be associated to the request UseCase: When an alarm is received indicating an issue with a server’s performance, with the ...

            • 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 ...

            • Project Time spent

              MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...

            • Set due by time in a request with value from a date time additional field - Deluge

              This custom function script is used to set the due by date in the request based on the value that is set in a date additional field.  This is performed using APIs for updating a request. UseCase:  Request due by time will be set based on Incident / ...