Dear All,
Please how can I amend the MS SQL query below to include:
- WorkOrder ID
- Created Time
and have the ability to generate the report on a monthly basis
SELECT AAAUSER.FIRST_NAME,COUNT(WORKORDER.WORKORDERID) 'Inbound',COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) <= 10 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) > 5 THEN 1 ELSE NULL END) '5 - 10 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) <= 15 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) > 10 THEN 1 ELSE NULL END) '10 - 15 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) <= 30 and DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 00:00:00'), getDate()) > 15 THEN 1 ELSE NULL END) '15 - 30 Days', COUNT(CASE when DATEDIFF(day, DATEADD(s, WORKORDER.CREATEDTIME/1000, '01-03-2017 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 AAAUSER ON WORKORDERSTATES.OWNERID=AAAUSER.USER_ID WHERE WORKORDERSTATES.STATUSID IN ( Select STATUSID from StatusDefinition where ISPENDING=1) GROUP BY AAAUSER.FIRST_NAME
ORDER BY AAAUSER.FIRST_NAME
Thank you.
Best regards,
Marge.