/build/static/layout/Breadcrumb_cap_w.png

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


1 Comment   [ + ] Show comment
  • Can you explain how you think the report will look? What columns would be included and what data is in each column? Perhaps include a few mock rows of data as an example? - chucksteel 3 years ago
    • Honestly, I would like to keep it simple. I only want it from one queue. The "IT" Queue and basically, the same as the one about but with the year after it.
      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 3 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 3 years ago
Red Belt
1

Top Answer

SELECT concat(month(CREATED), "/", year(CREATED)) as "Month/Year",
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 3 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 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ