Total number of K1000 completed tickets for each month/year by Priority with average time to complete
I am looking to create a report that takes the total number of closed tickets for each month with year listed, by Priority and average time to complete.
I am also looking for the average time to complete each priority group, to see if we are on target with what our guidelines state. Example - High priorities are stated - resolved within 4 hours, Medium - resolved within 2 business days, Low = 5 business days. Critical - <1 hour
Example -
January 2014 - 7 Critical - Average time to complete: .75 hours
January 2014 - 75 High - Average time to complete - 3.5 hours, etc.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
10 years ago
This should work:
SELECT concat(month(TIME_CLOSED), "/", year(TIME_CLOSED)) as "Month/Year", HD_PRIORITY.NAME as "Priority",count(HD_TICKET.ID) as "Tickets Closed",round(AVG(TIMESTAMPDIFF(SECOND, TIME_OPENED, TIME_CLOSED) )/3600, 2) AS AverageFROM ORG1.HD_TICKETJOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_IDWHERE HD_TICKET.HD_QUEUE_ID = 2and TIME_CLOSED != '0000-00-00 00:00:00'GROUP BY HD_PRIORITY_ID, YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)ORDER BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED);
Note that I'm limiting this to tickets in the queue ID = 2, you'll most likely have to change that for your KBOX.
