/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Report: Closed Tickets by Month as well as Queue

02/05/2020 295 views

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 YearCreated MonthIT_Helpdesk (Queue 1)NAV_Helpdesk (Queue 2)
120200112353


Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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.


Answered 02/05/2020 by: chucksteel
Red Belt

  • 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?
    • 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)

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