K1000 Service Desk report: Monthly closed tickets by owner and per year
Hey
I have found below SQL query in a previous topic showing the number of service desk tickets opened and closed each month per owner and for the last year.
I would like to return the same results not just from the last year, but over a period of more years. As my knowledge of SQL is not very profound I was wondering if you could help my adapt below query.
Thanks in advance!
SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
FROM HD_TICKET T
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) OPEN
LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, 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'
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) CLOSED
ON (OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
WHERE OPEN.YEAR = date_format(curdate(), '%Y')
ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER
FROM (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
FROM HD_TICKET T
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) OPEN
LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, 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'
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP BY OWNER_ID, MONTH, YEAR
ORDER BY YEAR, MONTH) CLOSED
ON (OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
WHERE OPEN.YEAR = date_format(curdate(), '%Y')
ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
reneed33
7 years ago
I am not a SQL guru, but I commented out this:
-- WHERE OPEN.YEAR = date_format(curdate(), '%Y')
And It gave me what I think you might be looking for. You could play with the grouping to display differently.
