/build/static/layout/Breadcrumb_cap_w.png
02/05/2016 647 views
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

Comments

  • Chuck, this is a great start for me. I appreciate the help immensely. I am becoming a big fan of ITNinja. I have two small requests, if possible. (I am a 3 man shop, so our SQL coding is very rusty, at best.) I would like to display this in hours, instead of days. Also, I would like a grand total of tickets closed and average. Is that easy to do?
    • The TIMESTAMPDIFF function's first argument is the unit of time to report. To report hours instead of days (really resisting the Star Trek Wrath of Khan references here) change:
      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
      • Thank you. As always, I was close, but no cigar. I put HOURS. That damned "S"!

All 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
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