/build/static/layout/Breadcrumb_cap_w.png

Count tickets created per month

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
  • 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 - aoh 6 years ago
  • 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 - aoh 6 years ago

Answers (1)

Posted by: chucksteel 6 years ago
Red Belt
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)



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