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...  :)
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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.

Answered 02/08/2016 by: chucksteel
Red Belt

  • That worked perfectly. You rock Chuck!!
  • Hi Chuck, is there a way to add additional attributes such as category to the report? I am just looking for a little more detail as to what kinds of tickets are taking how long to close.

    Thanks again
Please log in to comment
0
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)
;

Answered 02/08/2016 by: chucksteel
Red Belt

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