How can I display open tickets by queue with queues having zero tickets.
Need to run KACE Helpdesk report with open tickets by queues but also need to display queues having zero tickets.
Example:
Queue1 10
Queue2 20
Queue3 0
Queue4 10
I have SQL displaying queues with tickets open but cant make the rest of the queues to display zero's (0's).
SELECT
HD_QUEUE.NAME AS department,
IFNULL(COUNT(HD_TICKET.ID),0) AS count
FROM HD_TICKET
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE (HD_TICKET.HD_QUEUE_ID in (58,63,60,47,33,67,59,62,64)) AND (HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened')
GROUP BY department
Thanks in advance for the help.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
grayematter answer: SELECT HD_QUEUE.NAME AS department, IFNULL(tc.ticket_count, 0) AS count FROM HD_QUEUE LEFT JOIN (SELECT q.id, COUNT(t.ID) AS ticket_count FROM HD_TICKET t LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID LEFT JOIN HD_QUEUE q ON q.ID = t.HD_QUEUE_ID WHERE (HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened') GROUP BY t.HD_QUEUE_ID) tc ON tc.id = HD_QUEUE.ID WHERE (HD_QUEUE.ID in (58,63,60,47,33,67,59,62,64)) ORDER BY department
grayematter answer: SELECT HD_QUEUE.NAME AS department, IFNULL(tc.ticket_count, 0) AS count FROM HD_QUEUE LEFT JOIN (SELECT q.id, COUNT(t.ID) AS ticket_count FROM HD_TICKET t LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID LEFT JOIN HD_QUEUE q ON q.ID = t.HD_QUEUE_ID WHERE (HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened') GROUP BY t.HD_QUEUE_ID) tc ON tc.id = HD_QUEUE.ID WHERE (HD_QUEUE.ID in (58,63,60,47,33,67,59,62,64)) ORDER BY department
Please log in to answer
Posted by:
grayematter
10 years ago
Give this a try:
SELECT
HD_QUEUE.NAME AS department,
IFFNULL(tc.ticket_count, 0) AS count
FROM
HD_QUEUE
LEFT JOIN
(SELECT
q.id, COUNT(t.ID) AS ticket_count
FROM
HD_TICKET t
LEFT JOIN HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_QUEUE q ON q.ID = t.HD_QUEUE_ID
WHERE
(HD_STATUS.STATE = 'stalled'
OR HD_STATUS.STATE = 'opened')
GROUP BY t.HD_QUEUE_ID) tc ON tc.id = HD_QUEUE.ID
WHERE (HD_QUEUE.ID in (58,63,60,47,33,67,59,62,64))
ORDER BY department
Comments:
-
Just removed an extra F in IFFNULL and Worked Great!! Thanks grayematter. - rguerrer93 10 years ago
