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 workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition std ON wos.statusid=std.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.resolvedtime !='0' AND wo.CREATEDTIME >= <from_thisyear>
AND wo.CREATEDTIME <= <to_thisyear>
GROUP BY cd.categoryname, accountdefinition.org_name, wo.resolvedtime, wo.createdtime 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?
- 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
- 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 is applicable for 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 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 ...Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) 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", ...Query to show total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...