KACE Product Support Question
Custom Report: Closed Tickets by Month as well as Queue
I already got a Report that shows all closed Tickets each month by queue:
DATE_FORMAT(T.CREATED, '%Y') AS Created_Year,
DATE_FORMAT(T.CREATED, '%m') AS Created_Month,
HD_QUEUE.NAME AS 'Queue',
COUNT(T.id) AS 'Count'
HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
HD_STATUS ON (HD_STATUS.ID = T.HD_STATUS_ID)
GROUP BY Created_Year , Created_Month, HD_QUEUE.NAME
ORDER BY HD_QUEUE.NAME, Created_Year , Created_Month
What I am trying to do is put the two Queues as Columns with the count of created tickets each month.
So that it looks like this:
|#||Created Year||Created Month||IT_Helpdesk (Queue 1)||NAV_Helpdesk (Queue 2)|
Answer Chosen by the Author
Here's what I came up with:
SELECT YEAR(TIME_CLOSED) as 'Year',
MONTH(TIME_CLOSED) as 'Month',
SUM( CASE WHEN HD_QUEUE_ID = 2 THEN 1 ELSE 0 END) AS 'Queue 1',
SUM( CASE WHEN HD_QUEUE_ID = 7 THEN 1 ELSE 0 END) AS 'Queue 2'
GROUP BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)
Be sure to change the HD_QUEUE_ID = values to match your queue IDs and you can change the aliases appropriately.
I'm also grouping based on TIME_CLOSED instead of CREATED as you wanted counts for tickets closed, not opened.