Queue Reporting
We use the K1000 reporting tool, and below is our challenge:
We have a group that screens tickets as they are opened and either closes them, or transfers them to other queues as necessary. The screening team would like to be able to evaluate how many tickets they close vs. how many tickets they transfer. To meet their needs, I would like to generate a report that indicates the original queue in which the ticket was opened, and the queue in which the ticket was closed.
Please let me how you may have solved this problem, or any other approaches to getting to the right conclusion. Thanks!
1 Comment
[ + ] Show comment
-
Thanks chucksteel, that did it for me! I appreciate the help. - mankerbrandt 8 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
Here's a report that I created that captures tickets opened, closed and transferred per month:
SELECT YEAR(CREATED) AS TicketYears, MONTH(CREATED) AS TicketMonths, COUNT(ID) AS "Opened",
(SELECT COUNT(ID) FROM HD_TICKET WHERE YEAR(TIME_CLOSED) = TicketYears and MONTH(TIME_CLOSED) = TicketMonths and HD_QUEUE_ID = 2) AS "Closed",
(SELECT COUNT(ID) FROM HD_TICKET_CHANGE WHERE YEAR(TIMESTAMP) = TicketYears and MONTH(TIMESTAMP) = TicketMonths and DESCRIPTION like "%Changed ticket Queue from LIS Helpdesk to%") AS "Transferred"
FROM ORG1.HD_TICKET
WHERE HD_QUEUE_ID = 2
GROUP BY YEAR(CREATED), MONTH(CREATED)
Note that you need to make three changes for this to work for your environment:
There are two places where I specify HD_QUEUE_ID = 2. You must change these to match the ID of the queue you are targeting as the source queue.
The change description for transferring a ticket reads Changed ticket Queue from <source queue> to <target queue>. In my query <source queue> is LIS Helpdesk, you need to change that to match the name of your queue.
Note that there is a potential flaw with this query. If a ticket is transferred from the source queue to a target queue, then back to the source queue, then back to the target queue it will be counted in the transfer column twice.
For a simple report of tickets transferred in the past month you can use this:
SELECT HD_TICKET.ID, HD_TICKET.TITLE,
SUBSTRING_INDEX(HD_TICKET_CHANGE.DESCRIPTION, ".", 1) AS "Transfered"
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
WHERE HD_TICKET_CHANGE.DESCRIPTION like "%Changed ticket Queue%"
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 MONTH)
This will show all transfers regardless of source and target queue.
