k1000 Report Help
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
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
