/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Count tickets created per month

03/06/2018 576 views
Hi guys, trying to create a report that can display total tickets created per month also a seperate report that can show total tickets closed per month. I found many reports but they are combined (created and closed) I tried modifying the combined one to have two seperate reports but it failed. I appreciate your help.
2 Comments   [ + ] Show comments

Comments

  • I have this code and it is producing error, "Unknown Cloumn T.CUSTM_FIELD_VALUE3" This field does exist in the table T.
    SELECT OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED
    FROM (SELECT date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
    FROM HD_TICKET T
    GROUP BY MONTH, YEAR
    ORDER BY YEAR, MONTH) OPEN
    WHERE OPEN.YEAR = date_format(curdate(), '%Y') and
    T.CUSTOM_FIELD_VALUE3 = 'Incident'
    ORDER BY YEAR asc, str_to_date(OPEN.MONTH,'%M') asc
  • Thank you Chuck. Will give it ago. I will need to add the where statement to filter ticket type (incident, request) for created and closed

All Answers

1
Here is the simplest report for tickets opened per month:
SELECT YEAR(CREATED), MONTH(CREATED), COUNT(ID)
FROM HD_TICKET
GROUP BY YEAR(CREATED), MONTH(CREATED)
ORDER BY YEAR(CREATED), MONTH(CREATED)
And closed per month:
SELECT YEAR(TIME_CLOSED), MONTH(TIME_CLOSED), COUNT(ID)
FROM HD_TICKET
WHERE TIME_CLOSED != '0000-00-00 00:00:00'
GROUP BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)



Answered 03/07/2018 by: chucksteel
Red Belt

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