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

Comments

Please log in to comment

Answers

-1

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.

 

Answered 02/12/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity