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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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)
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