Help with SQL Kace Report
Need a report to show closed tickets by queues but also show tickets that were submitted by technician (in that queue), not submitted by technician (in that queue). We currently have something like that but I would like to see it by queues and not by technicians.
Any help is greatly appreciated, thank you in advance.
Example:
| Name Of Queue: | Submitted by technician: | Not submitted by technician: | Submitted: |
| Queue 1 Queue 2 | 5 10 | 3 15 | 8 25 |
SELECT TICKETOWNERS.Technician,
COALESCE(Tech_Submitter.Count, 0) as 'Tickets Submitted By Technician',
COALESCE(User_Submitter.Count, 0) as 'Tickets Not Submitted by Technician',
COALESCE(TOTALTICKET.Count, 0) as 'Tickets Assigned to Technician'
FROM (SELECT USER.ID as 'TICKETOWNER_ID', FULL_NAME AS 'Technician', Count(USER.ID) as 'Count'
FROM USER
WHERE USER.ROLE_ID IN ('1','15','16')
Group By TICKETOWNER_ID, Technician) TICKETOWNERS
LEFT JOIN (SELECT HD_TICKET.OWNER_ID as 'TICKETOWNER_ID', FULL_NAME AS 'Technician', Count(HD_TICKET.ID) as 'Count'
FROM USER, HD_TICKET
WHERE USER.ROLE_ID IN ('1','15','16')
AND HD_TICKET.Owner_ID = USER.ID
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
Group By TICKETOWNER_ID, Technician) TOTALTICKET On (TICKETOWNERS.TICKETOWNER_ID = TOTALTICKET.TICKETOWNER_ID and TICKETOWNERS.Technician = TOTALTICKET.Technician)
LEFT JOIN (SELECT HD_TICKET.OWNER_ID as 'TICKETOWNER_ID', FULL_NAME AS 'Technician', Count(HD_TICKET.ID) as 'Count'
FROM USER, HD_TICKET
WHERE USER.ROLE_ID IN ('1','15','16') AND
HD_TICKET.SUBMITTER_ID = HD_TICKET.OWNER_ID
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
Group By TICKETOWNER_ID, Technician) Tech_Submitter On (TICKETOWNERS.TICKETOWNER_ID = Tech_Submitter.TICKETOWNER_ID and TICKETOWNERS.Technician = Tech_Submitter.Technician)
LEFT JOIN (SELECT HD_TICKET.OWNER_ID as 'TICKETOWNER_ID', FULL_NAME AS 'Technician', Count(HD_TICKET.ID) as 'Count'
FROM USER, HD_TICKET
WHERE USER.ROLE_ID IN ('1','15','16') AND
HD_TICKET.SUBMITTER_ID != HD_TICKET.OWNER_ID
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
Group By TICKETOWNER_ID, Technician) User_Submitter On (TICKETOWNERS.TICKETOWNER_ID = User_Submitter.TICKETOWNER_ID and TICKETOWNERS.Technician = User_Submitter.Technician)
Order By TICKETOWNERS.Technician
1 Comment
[ + ] Show comment
-
When you say submitted by a technician do you want to know if they were the submitter or are you trying to track number of tickets created by technicians vs created by others? - chucksteel 10 years ago
-
Track tickets created by others. - mike.martinez 10 years ago
Answers (2)
Please log in to answer
Posted by:
chucksteel
10 years ago
This was challenging but I think I have it:
SELECT HD_TICKET.HD_QUEUE_ID, HD_QUEUE.NAME,
sum(if(find_in_set(HD_TICKET.SUBMITTER_ID, (SELECT group_concat(USER_LABEL_JT.USER_ID) as "Owner IDs"
FROM HD_QUEUE
LEFT JOIN HD_QUEUE_OWNER_LABEL_JT on HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID = HD_QUEUE.ID
JOIN LABEL L on L.ID = HD_QUEUE_OWNER_LABEL_JT.LABEL_ID
JOIN USER_LABEL_JT on USER_LABEL_JT.LABEL_ID = L.ID
WHERE HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID)) > 0, 1, 0)) as "Technicians",
sum(if(find_in_set(HD_TICKET.SUBMITTER_ID, (SELECT group_concat(USER_LABEL_JT.USER_ID) as "Owner IDs"
FROM HD_QUEUE
LEFT JOIN HD_QUEUE_OWNER_LABEL_JT on HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID = HD_QUEUE.ID
JOIN LABEL L on L.ID = HD_QUEUE_OWNER_LABEL_JT.LABEL_ID
JOIN USER_LABEL_JT on USER_LABEL_JT.LABEL_ID = L.ID
WHERE HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID)) = 0, 1, 0)) as "Others",
count(HD_TICKET.ID) as "Total"
FROM HD_TICKET
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
GROUP BY HD_TICKET.HD_QUEUE_ID