Hi ,

I would like some help modifying the report below to separate the total tickets per month by support queue as well. Any assistance would be appreciated. 


http://www.itninja.com/question/report-of-total-tickets-per-month-for-the-year


*SQL Select Statement*
SELECT OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED
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
LEFT JOIN (SELECT date_format(T.TIME_CLOSED, '%M') AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
FROM HD_TICKET T
JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
GROUP BY MONTH, YEAR
ORDER BY YEAR, MONTH) CLOSED
ON (OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR)
WHERE OPEN.YEAR = date_format(curdate(), '%Y')
ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share