/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 Service Desk report: Monthly closed tickets by owner and per year

02/03/2017 1239 views
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

0 Comments   [ + ] Show comments

Comments


All Answers

0
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. 
Answered 02/03/2017 by: reneed33
Senior Yellow Belt

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