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?
- 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>
Last Tested on Builds: 14500 and above.