I'm working on a report to show how many times a ticket has been reopened.  Ideally, I would be able to see an open and close date for each open and close on the ticket, and from there I could generate the report.  At a minimum, being able to report the number of times a given ticket has been reopened would be helpful.  Please let me know if you have any solutions for this!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
I came up with this report:
SELECT T.ID, COUNT(DISTINCT(CLOSED.ID)) AS TIMESCLOSED, COUNT(DISTINCT(REOPENED.ID)) AS TIMESREOPENED, T.TITLE, T.CREATED,
GROUP_CONCAT(DISTINCT(REOPENED.TIMESTAMP)) AS WHENREOPENED,
GROUP_CONCAT(DISTINCT(CLOSED.TIMESTAMP)) AS WHENCLOSED
FROM 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 HD_TICKET_CHANGE REOPENED on REOPENED.HD_TICKET_ID = T.ID and REOPENED.DESCRIPTION like '%Changed ticket Status from "Closed" to%'
GROUP BY T.ID
HAVING TIMESCLOSED > 1
ORDER BY TIMESCLOSED DESC

Answered 02/22/2016 by: chucksteel
Red Belt

  • Awesome, that did it! Thanks so much for your help chucksteel.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share