Hi there - I am looking for a way to remove non-operational hours from a report showing average response time per support group:
SELECT qd.queuename "Group",
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 responseTime" FROM workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sdf ON wos.statusid=sdf.statusid
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE wo.respondedtime!='0'
AND wo.CREATEDTIME >= <from_thismonth>
AND wo.CREATEDTIME <= <to_thismonth>
GROUP BY qd.queuename