K1000 Reporting - Need a report to track Time Created, when ticket changed queue, and when the ticket status changed
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 Chosen by the Author
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"
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.