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.
    • This content is currently hidden from public view.
      Reason: Removed by member request
      For more information, visit our FAQ's.
    • Hello, I know this thread is pretty old I am attempting to make the following changes to this report:
      - group this report by Owner of the ticket
      - add the feedback on the ticket on this report.
      - Restrict the report to the last 30 days.

      Any help would be appreciated.
      • I was able to get the query started, but I am having an issue with grouping by owner and restricting by 30 days.




        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, ifnull((select FULL_NAME from USER where T.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,T.CREATED as Created
        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
      • If I understand correctly you want the number of tickets per owner that were re-opened? If it were grouped by owner then including feedback on individual tickets could result in a rather long text field, are you sure that is what you want?
Please log in to comment

Answers

0
Thanks for the request for clarification question. I am looking for the feedback score not all of the text associated with the feedback. 
Answered 12/06/2016 by: solarissparc
Senior White Belt

  • A comma separated list or average?
    • Feedback for each ticket that was reopened. I assumed that it will be only one feedback score per ticket.
      • Right, but you also said grouped by owner, did you mean that you want the owner displayed in the report?
Please log in to comment
0
Yes I was thinking something like:

Ticket owner:
    1  .. . . ..
Ticket owner 2:
    1  .. . . ..
    2   .. . . ..
    3  .. . . ..
Answered 12/06/2016 by: solarissparc
Senior White Belt

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

Share