Pending Request Age by Group not Working, please find the below given query.
SELECT QUEUEDEFINITION.QUEUENAME,COUNT(WORKORDER.WORKORDERID) 'Total Pending Count',COUNT(CASE WHEN DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 2 THEN 1 ELSE NULL END) '0 - 2 Days',COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 5 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 2 THEN 1 ELSE NULL END) '2 - 5 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 10 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 5 THEN 1 ELSE NULL END) '5 - 10 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 15 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 10 THEN 1 ELSE NULL END) '10 - 15 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 30 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 15 THEN 1 ELSE NULL END) '15 - 30 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 30 THEN 1 ELSE NULL END) 'More than 30 Days' FROM WORKORDER INNER JOIN WORKORDERSTATES on WORKORDERSTATES.WORKORDERID = WORKORDER.WORKORDERID LEFT JOIN STATUSDEFINITION on STATUSDEFINITION.STATUSID = WORKORDERSTATES.STATUSID LEFT JOIN WORKORDER_QUEUE ON WORKORDER.WORKORDERID = WORKORDER_QUEUE.WORKORDERID LEFT JOIN QUEUEDEFINITION ON WORKORDER_QUEUE.QUEUEID=QUEUEDEFINITION.QUEUEID WHERE WORKORDERSTATES.STATUSID IN ( Select STATUSID from StatusDefinition where ISPENDING=1) GROUP BY QUEUEDEFINITION.QUEUENAME
ORDER BY QUEUEDEFINITION.QUEUENAME