Query to show Average response time for Category

Query to show Average response time for Category

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 workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sdf ON wos.statusid=sdf.statusid
LEFT JOIN prioritydefinition pd ON wos.priorityid=pd.priorityid
LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid
LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN Categorydefinition cd ON wos.categoryid=cd.categoryid
WHERE wo.respondedtime!='0' AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
GROUP BY cd.categoryname, accountdefinition.org_name ORDER BY 1

MSSQL:

SELECT qd.QUEUENAME "Group",
ti.FIRST_NAME "Technician",
count(wo.WORKORDERID) "Tickets Count",
convert(varchar(10),(avg(wo.respondedtime)-avg(wo.createdtime))/1000/3600)+':'+convert(varchar(10),((avg(wo.respondedtime)-avg(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),(((avg(wo.respondedtime)-avg(wo.createdtime)))/1000%60)) "Avg Time taken to respond" FROM WorkOrder wo 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (wo.ISPARENT='1') and wo.respondedtime!=0 and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
group by qd.queuename,ti.FIRST_NAME order by 1
‚Äč

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>
          • Related Articles

          • 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 ...
          • Query to show Average resolution time for Category

            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 workorder wo LEFT JOIN workorderstates wos ON ...
          • Query to show tickets created based on shift time_PGSQL

            Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...
          • Query to show total time spent of a technician for the current month_PGSQL

            Query show total time spent by technician for the current month regardless of the ticket created date. PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS ...
          • 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 = ...