I am looking for some help with a report for a count of all tickets created by category with the categories default owner listed as well. I have a script below but it doesn't seem to list categories with a zero ticket count, and my feeble attempts to add the default owners fail every time. Any help is appreciated. 

SELECT HD_QUEUE.NAME as 'Queue',
HD_CATEGORY.NAME as 'Category',
count(HD_TICKET.id) as 'Count'
FROM HD_QUEUE
INNER JOIN (HD_TICKET 
INNER JOIN HD_CATEGORY ON HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
Group BY HD_QUEUE.NAME, HD_CATEGORY.NAME
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME;
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This will show the tickets just for the past month:
SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME AS Category, 
COUNT(HD_TICKET.ID) as Tickets, USER.FULL_NAME as "Default Owner"
FROM ORG1.HD_TICKET
LEFT JOIN HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
LEFT JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN USER on USER.ID = HD_CATEGORY.DEFAULT_OWNER_ID
WHERE HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY HD_CATEGORY.ID
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME
Note that it will only show categories that have tickets in the past month. If you want to show all categories then it's easiest to start with the HD_CATEGORY table, like this:
SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME AS Category, 
(SELECT COUNT(ID) FROM HD_TICKET where 
HD_CATEGORY_ID = HD_CATEGORY.ID 
and HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)GROUP BY HD_CATEGORY.ID) as Tickets, 
USER.FULL_NAME as "Default Owner"
FROM HD_CATEGORY
LEFT JOIN HD_QUEUE on HD_QUEUE.ID = HD_CATEGORY.HD_QUEUE_ID
LEFT JOIN USER on USER.ID = HD_CATEGORY.DEFAULT_OWNER_ID

GROUP BY HD_CATEGORY.ID
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME




Answered 11/21/2017 by: chucksteel
Red Belt

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