Report of Total Tickets per Month per support queue for the Year
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.
*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
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
Answers (0)
Please log in to answer
Be the first to answer this question
