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")

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • I am trying to edit grayematter's code in mysql workbench but I am getting an error for user R1 Error Code: 1142. SELECT command denied to user 'R1'@'nrsranch-it01.goropin.local' for table 'HD_TICKET_CHANGE'

    I have changed the password for R1 to verify it is the correct password.
    any Ideas
Please log in to comment

Answer Chosen by the Author

0
Thank you very much, that script works great! Is there a way to only show closed tickets from 2 specified users?
Answered 07/07/2016 by: joshuaer
Senior Yellow Belt

  • Add
    AND (CLOSER.USER_NAME = "jwinn" or CLOSER.USER_NAME = "twinn")
    just before the "GROUP BY" line.
Please log in to comment

Answers

0

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)
Answered 07/07/2016 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity