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 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 Onhold time_PGSQL

            PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", ...
          • Query to show total time taken to resolve and total time onhold _ MSSQL

            MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "urgdef"."NAME" AS "Urgency", ...
          • Query to show Category, Sub category and Item

            PGSQL & MSSQL: SELECT CategoryDefinition.CATEGORYNAME "Category Name", CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME "Sub Category Name" ,ItemDefinition.NAME"Item Name" FROM CategoryDefinition left join ...
          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...