Report showing/listing all available categories count tickets per category per month current year
Ttrying to create s report that will list all categories including null tickets count tickets for each category put 0 if no tickets for any of the categories for each month for current year.
Thank you in advance.
2 Comments
[ + ] Show comments
-
For one specific queue or for all queues? - chucksteel 6 years ago
-
There’s only one queue Chuck. - aoh 6 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
Here you go:
SELECT HD_CATEGORY.NAME AS Category,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 1 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS January,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 2 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS February,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 3 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS March,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 4 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS April,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 5 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS May,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 6 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS June,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 7 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS July,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 8 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS August,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 9 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS September,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 10 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS October,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 11 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS November,
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and MONTH(HD_TICKET.CREATED) = 12 and YEAR(HD_TICKET.CREATED) = YEAR(NOW())) AS December
FROM HD_CATEGORY
ORDER BY HD_CATEGORY.NAME
Comments:
-
Thank you Chuck. Appreciate it. - aoh 6 years ago
