K1000 Reporting - Need a report to track Time Created, when ticket changed queue, and when the ticket status changed
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!
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!
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
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.
Comments:
-
THANK YOU very much for your time! - kimimtt 8 years ago
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? - Hobbsy 8 years ago
Thank you - kimimtt 8 years ago