Custom Report: Closed Tickets by Month as well as Queue
Hi There
I already got a Report that shows all closed Tickets each month by queue:
SELECT
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'
FROM
HD_QUEUE
INNER JOIN
HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
JOIN
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) |
| 1 | 2020 | 01 | 123 | 53 |
Answers (1)
Top Answer
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'
FROM HD_TICKET
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.
Comments:
-
That's exactly the direction I wanted to take.
I wrote that wrong - I wanted TIME_OPENED. But this I am able to change myself.
One thing I just realized is that the values that I get back are slightly different then with my SQL Statement. Do you know why that could be? I mean it's from the same Tables... so it should be the same...?
EDIT: Oh, and would it be possible to add archived tickets? - e.meier 4 years ago-
I'm not sure why the values would be different, but I suspect it may be that I'm selecting directly from the HD_TICKET table whereas you are starting with HD_QUEUE and joining to the others.
This will include archived tickets:
SELECT YEAR(TIME_OPENED) as 'Year',
MONTH(TIME_OPENED) 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'
FROM (
(SELECT TIME_OPENED,
HD_QUEUE_ID
FROM HD_TICKET)
UNION
(SELECT TIME_OPENED,
HD_QUEUE_ID
FROM HD_ARCHIVE_TICKET)
) t
GROUP BY YEAR(TIME_OPENED), MONTH(TIME_OPENED)
ORDER BY YEAR(TIME_OPENED), MONTH(TIME_OPENED) - chucksteel 4 years ago
