Run a report comparing Tickets Closed for the last two years per month
We use KACE Systems Management Appliance and looking to compare this years tickets to last year and break up by month. I have tried the below report but that just gives the latest year and if I make it 24 months, it only appends the numbers to the months.
SELECT
COUNT(HD_TICKET.ID) AS NUMBER_OPENED,
date_format(HD_TICKET.CREATED, '%M') AS MONTH_OPENED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) >= DATE_SUB(NOW(), INTERVAL 12 MONTH)))
GROUP BY MONTH_OPENED
ORDER BY MONTH_OPENED
Answers (1)
Top Answer
COUNT(HD_TICKET.ID) as "Tickets Opened"
FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 2
AND CREATED > date_sub(NOW(), INTERVAL 24 MONTH)
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)
Be sure to change the Queue ID to match the ID for your IT queue.
Comments:
-
That's it! Thank you so much Chuck!
Unfortunately, it looks like KACE must archive after a year or something. The numbers drop to double digits after a year. No way to pull that data too, or is that untouchable?
9 6/2019 15
10 7/2019 14
11 8/2019 21
12 9/2019 52
13 10/2019 866
14 11/2019 700
15 12/2019 574
16 1/2020 808 - WDills 4 years ago-
Never mind. I found the setting in the queue. It was set to 1 year. We have fixed that. Thank you Chuck for the reply. Works perfect. - WDills 4 years ago
So:
Month / Year / # of closed tickets
I have tried the one below and it breaks things down a lot more than I need but is ok. However, they will only go 12 months back also.
select case
when MONTH(T.CREATED) between 1 and 3 then '1st Quarter'
when MONTH(T.CREATED) between 4 and 6 then '2nd Quarter'
when MONTH(T.CREATED) between 7 and 9 then '3rd Quarter'
when MONTH(T.CREATED) between 10 and 12 then '4th Quarter'
end as QUARTER,
C.NAME,
COUNT(T.ID) as NUMBER_TICKETS
from HD_TICKET T
join HD_CATEGORY C on T.HD_CATEGORY_ID = C.ID
where T.HD_QUEUE_ID = 1
and YEAR(T.CREATED) = YEAR(CURTIME())
group by QUARTER,
T.HD_CATEGORY_ID
order by QUARTER asc - WDills 4 years ago