I've been struggling with this and trying to modify other reports and sql statements I found in other posts, however, nothing is working. This is what I am looking for:

1. Ticket Number
2. Description/Problem (title)
3. Date/Time ticket was created
4. Date/Time ticket queue changed and by whom
5. Date/Time ticket status changed
6. All status'

Any assistance is appreciated!
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • What you are asking for is quite a complex query which is probably why you have not found an answer from any other posts. If I take your request at face value, to form the report we need to pull data from
    HD_TICKET,
    USER,
    HD_TICKET_CHANGE
    and HD_STATUS tables
    but before we can suggest any code I would like to understand why you need the report, why the need to record the change of queue, change of status and all other status (?) on a single report?

    Surely a single detailed ticket report showing the full history will provide you with all the detail you require?
  • A second level support group has their own queue. They would like to know how long it is taking the Help Desk to move tickets into their queue. We can live without status change as a criteria but would need current status as a column on the report. So I need to know when the ticket was created, when it was moved into queue 12 and by whom. Everything listed above except #5.

    Thank you
Please log in to comment

Answer Chosen by the Author

0
This query will show all tickets that have been transferred from one queue to another in the past month:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CREATED as "Created",
HD_TICKET_CHANGE.TIMESTAMP as "Transferred",
CHANGER.FULL_NAME as "Changer",
HD_STATUS.NAME as "Current Status"
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_STATUS on HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
JOIN USER CHANGER on CHANGER.ID = HD_TICKET_CHANGE.USER_ID
WHERE HD_TICKET_CHANGE.DESCRIPTION like "%Changed ticket Queue%"
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 MONTH)

To show tickets being moved from a specific queue into another change the "%Changed ticket Queue%" to "%Changed ticket Queue from Tier one queue to Tier two queue%" as appropriate. Queue changes are not tracked in HD_TICKET_CHANGE_FIELD so you need to look for the change in HD_TICKET_CHANGE descriptions.


Answered 01/28/2016 by: chucksteel
Red Belt

  • THANK YOU very much for your time!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity