/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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

Comments

  • For one specific queue or for all queues?
  • There‚Äôs only one queue Chuck.

All Answers

0
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


Answered 08/29/2018 by: chucksteel
Red Belt

  • Thank you Chuck. Appreciate it.