Aging Tickets per Technician per Group
Hello,
I found the built in Query Report for aging ticket by technician:
SELECT AAAUSER.FIRST_NAME,COUNT(WORKORDER.WORKORDERID) "Total Pending Count",COUNT(CASE WHEN DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 2 THEN 1 ELSE NULL END) "0 - 2 Days",COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 5 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 2 THEN 1 ELSE NULL END) "2 - 5 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 10 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 5 THEN 1 ELSE NULL END) "5 - 10 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 15 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 10 THEN 1 ELSE NULL END) "10 - 15 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 30 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 15 THEN 1 ELSE NULL END) "15 - 30 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 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=true) GROUP BY AAAUSER.FIRST_NAME
ORDER BY AAAUSER.FIRST_NAME
And this runs great.
I have a request to create a report for aging tickets by technician for each of our supervisors. I was thinking I could just create groups for each of the supervisors and then add each technician to their supervisor's group. Then I was hoping to run the above query but filtered by a specific supervisor group. The issue I'm running into is that I understand what this query is doing but my SQL is a bit rusty and I'm not sure how to add the filter for the specific group.
Any help with this would be much appreciated.
I found the built in Query Report for aging ticket by technician:
SELECT AAAUSER.FIRST_NAME,COUNT(WORKORDER.WORKORDERID) "Total Pending Count",COUNT(CASE WHEN DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 2 THEN 1 ELSE NULL END) "0 - 2 Days",COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 5 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 2 THEN 1 ELSE NULL END) "2 - 5 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 10 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 5 THEN 1 ELSE NULL END) "5 - 10 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 15 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 10 THEN 1 ELSE NULL END) "10 - 15 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) <= 30 and DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 15 THEN 1 ELSE NULL END) "15 - 30 Days", COUNT(CASE when DATE_PART('day', now() - FROM_UNIXTIME(WORKORDER.CREATEDTIME/1000)) > 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=true) GROUP BY AAAUSER.FIRST_NAME
ORDER BY AAAUSER.FIRST_NAME
And this runs great.
I have a request to create a report for aging tickets by technician for each of our supervisors. I was thinking I could just create groups for each of the supervisors and then add each technician to their supervisor's group. Then I was hoping to run the above query but filtered by a specific supervisor group. The issue I'm running into is that I understand what this query is doing but my SQL is a bit rusty and I'm not sure how to add the filter for the specific group.
Any help with this would be much appreciated.