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

Comments

  • 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?
Please log in to comment

Community Chosen Answer

1
Thank you so much.  
Answered 10/10/2014 by: mike.martinez
White Belt

Please log in to comment

Answers

0
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
Answered 10/09/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity