I need to build a report with average "ticket open" time for a specific month or date range
My goal here is to show improvement (or not) in the time to resolution for tickets in a specific queue. Can anyone assist me in putting something together that would tell me the average time it took for tickets to be closed?
Thanks a bunch to the people smarter than me... :)
1 Comment
[ + ] Show comment
Answers (2)
Please log in to answer
Posted by:
chucksteel
8 years ago
This should work:
SELECT concat(month(TIME_CLOSED), "/", year(TIME_CLOSED)) as "Month/Year",
count(HD_TICKET.ID) as "Tickets Closed",
AVG(TIMESTAMPDIFF(DAY,
TIME_OPENED,
TIME_CLOSED)
) AS Average
FROM ORG1.HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 2
and TIME_CLOSED != '0000-00-00 00:00:00'
and HD_QUEUE_ID = 2
GROUP BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
;
This is for HD_QUEUE_ID = 2.
Posted by:
chucksteel
8 years ago
This report includes the category:
SELECT concat(month(TIME_CLOSED), "/", year(TIME_CLOSED)) as "Month/Year", HD_CATEGORY.NAME as "Category",
count(HD_TICKET.ID) as "Tickets Closed",
AVG(TIMESTAMPDIFF(DAY,
TIME_OPENED,
TIME_CLOSED)
) AS AverageTimeToClose
FROM ORG1.HD_TICKET
JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
and TIME_CLOSED != '0000-00-00 00:00:00'
and HD_TICKET.HD_QUEUE_ID = 2
GROUP BY HD_CATEGORY_ID, YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
;
TIMESTAMPDIFF(DAY, TIME_OPENED, TIME_CLOSED)
to:
TIMESTAMPDIFF(HOUR, TIME_OPENED, TIME_CLOSED)
Here is the documentation on the function.
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff - chucksteel 5 years ago