Query to show Average response time for Technicians

Query to show Average response time for Technicians

PGSQL:

SELECT aau.first_name "Technician",
       TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg response 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.respondedtime !='0'  AND wo.CREATEDTIME >= <from_thisyear>
  AND wo.CREATEDTIME <= <to_thisyear>
GROUP BY aau.first_name


NOTE: Date filter  highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>

Query works on latest builds too

                    New to ADSelfService Plus?

                      • Related Articles

                      • Query to show Average resolution time for Technicians

                        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 ...
                      • Query to show Average response time for Category (MSSQL & PGSQL)

                        Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...
                      • Query to show Average resolution time for Category (PGSQL)

                        Tested in PGSQL build (14300) Databases: PGSQL SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.resolvedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg resolution Time" FROM ...
                      • 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 ...
                      • Query to retrieve response time in minutes

                        To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...