Exclude non-operational hours from a report

Exclude non-operational hours from a report

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
 

                New to ADManager Plus?

                  New to ADSelfService Plus?