/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 6 years ago
Red Belt
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




Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ