KACE - Avg Ticket Time report help
Ok I am not good with SQL but I managed to modify someone elses report to only show the # of tickets for each queue and the avg close time. My question is, how do I get the report to show the name of the queue instead of the queue ID?
Here is the report query:
SELECT HD_TICKET.HD_QUEUE_ID as QUEUE NAME, count(HD_TICKET.ID) AS NUMBER_OF_TICKETS, IF ( DATE(TIME_OPENED) = DATE(TIME_CLOSED), CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, IF (TIME_OPENED = '0', TIME_CLOSED, TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'), CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - ( SELECT COUNT(*) FROM HD_SLA_HOLIDAYS WHERE (WEEKDAY(VALUE) < 5) AND ( VALUE BETWEEN TIME_OPENED AND TIME_CLOSED ) ))/COUNT(HD_TICKET.ID), 1))), ' Days') ) AS AVG_TIME_TO_CLOSE FROM HD_TICKET LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID WHERE HD_STATUS. NAME = 'Closed' AND TIME_CLOSED <> 0 AND TIME_CLOSED > '2017-05-01' /*change the start date here*/ AND TIME_CLOSED < '2017-12-01' /*change the end date here*/ AND HD_TICKET.HD_QUEUE_ID IN (1,2) /*add queue numbers here separated by commas*/ GROUP BY HD_TICKET.HD_QUEUE_ID |
The report looks like this:
# Hd Queue Id Number Of Tickets Avg Time To Close
1 1 133 1.1 Days
2 2 44 3.8 Days
Under "HD Queue ID" I would like it to show an actual queue name (IT HELP DESK, ETC).
This is probably really easy but I have no clue lol. I know just enough to be dangerous. Thanks in advance!
EDIT:
OK I was able to get the query working by adding a CASE to the select as follows:
SELECT
CASE
WHEN HD_TICKET.HD_QUEUE_ID = 1 THEN 'IT Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 2 THEN 'BAIT Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 10 THEN 'Accounting Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 8 THEN 'Card ServicesHelpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 7 THEN 'Facilities Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 11 THEN 'HR Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 3 THEN 'Marketing Requests'
WHEN HD_TICKET.HD_QUEUE_ID = 12 THEN 'Meridian Link Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 4 THEN 'PDC Helpdesk'
WHEN HD_TICKET.HD_QUEUE_ID = 6 THEN 'Reporting Helpdesk'
ELSE 'unknown'
END AS 'Queue Name',
That now generates this:
# Queue Name Number Of Tickets Avg Time To Close
1 IT Helpdesk 143 1.0 Days
2 BAIT Helpdesk 45 3.8 Days
3 Marketing Requests 8 12.6 Days
4 Facilities Helpdesk 7
5 Meridian Link Helpdesk 3 0.3 Days
Now I am having a new Issue.
1- The facilities helpdesk line doesn't have an avg time to close, weird.
2- If I change the date (AND TIME_CLOSED < '2017-12-01') to '2017-6-17' then I only get 1 results and I know thats not right.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
The queue's name is stored in the HD_QUEUE table, so you need to create a join to that table:
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
This line goes with the other join statements.
With that in place you can select columns from the HD_QUEUE table, like the queue's name:
HD_QUEUE.NAME as "Queue Name"
This line goes with the other selected columns.
Your complete SQL statement should look like this:
SELECT
HD_TICKET.HD_QUEUE_ID as "Queue ID",
HD_QUEUE.NAME as "Queue Name",
count(HD_TICKET.ID) AS NUMBER_OF_TICKETS,
IF (
DATE(TIME_OPENED) = DATE(TIME_CLOSED),
CONCAT(ROUND((SUM(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, IF (TIME_OPENED = '0', TIME_CLOSED, TIME_OPENED)))) / COUNT(HD_TICKET.ID) MOD 86400) / 3600, 1), ' Hours'),
CONCAT((GREATEST(0, ROUND(SUM(5 * (DATEDIFF(TIME_CLOSED, TIME_OPENED) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(TIME_OPENED) + WEEKDAY(TIME_CLOSED) + 1, 1) - (
SELECT
COUNT(*)
FROM
HD_SLA_HOLIDAYS
WHERE
(WEEKDAY(VALUE) < 5)
AND (
VALUE BETWEEN TIME_OPENED AND TIME_CLOSED
)
))/COUNT(HD_TICKET.ID), 1))), ' Days')
) AS AVG_TIME_TO_CLOSE
FROM
HD_TICKET
LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE
HD_STATUS. NAME = 'Closed'
AND TIME_CLOSED <> 0
AND TIME_CLOSED > '2017-05-01' /*change the start date here*/
AND TIME_CLOSED < '2017-12-01' /*change the end date here*/
AND HD_TICKET.HD_QUEUE_ID IN (1,2) /*add queue numbers here separated by commas*/
GROUP BY
HD_TICKET.HD_QUEUE_ID
I also changed the alias for the queue ID.
Comments:
-
Much more elegant than my fix lol. - LeperMessiah 7 years ago
-
One more question if I may. The 2nd CONCAT statement. If I want it to show HOURS instead of DAYS, how would I do that? I tried modifying it and couldnt get it to work. - LeperMessiah 7 years ago
