Report shows closed tickets
Hello All
I would like to create a report that shows the total amount of closed for each month for the past year, for example.
october 400
november 425
december 410
we currently run this script to show the amount of tickets closed for the previous day
SELECT T.ID, T.CREATED, T.TIME_CLOSED, OWNER.FULL_NAME as "Owner",
CLOSED.TIMESTAMP, CLOSED.DESCRIPTION,
CLOSER.FULL_NAME as "Closer"
FROM ORG1.HD_TICKET T
LEFT JOIN HD_TICKET_CHANGE CLOSED ON CLOSED.HD_TICKET_ID = T.ID and CLOSED.DESCRIPTION like '%Changed ticket Status from%to "Closed"%'
LEFT JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
LEFT JOIN USER CLOSER ON CLOSER.ID = CLOSED.USER_ID
WHERE T.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND (CLOSER.USER_NAME = "jwinn" or CLOSER.USER_NAME = "twinn")
1 Comment
[ + ] Show comment
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
grayematter
7 years ago
This should give you what you need. If you want to limit the counts to closers "jwinn" or "twinn", just add that line from your original query above.
SELECT
YEAR(T.TIME_CLOSED) AS 'Closed Year',
MONTH(T.TIME_CLOSED) AS 'Closed Month',
COUNT(T.ID) AS 'Closed Count'
FROM
ORG1.HD_TICKET T
LEFT JOIN
HD_TICKET_CHANGE CLOSED ON CLOSED.HD_TICKET_ID = T.ID
AND CLOSED.DESCRIPTION LIKE '%Changed ticket Status from%to "Closed"%'
LEFT JOIN
USER OWNER ON OWNER.ID = T.OWNER_ID
LEFT JOIN
USER CLOSER ON CLOSER.ID = CLOSED.USER_ID
WHERE
T.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY YEAR(T.TIME_CLOSED) , MONTH(T.TIME_CLOSED)
Posted by:
joshuaer
7 years ago
Top Answer
Thank you very much, that script works great! Is there a way to only show closed tickets from 2 specified users?
Comments:
-
Add
AND (CLOSER.USER_NAME = "jwinn" or CLOSER.USER_NAME = "twinn")
just before the "GROUP BY" line. - grayematter 7 years ago
I have changed the password for R1 to verify it is the correct password.
any Ideas - joshuaer 7 years ago