Aged Calls per Technician and Unassigned

Aged Calls per Technician and Unassigned

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.
              New to ADManager Plus?

                New to ADSelfService Plus?