/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


I need to build a report with average "ticket open" time for a specific month or date range

02/05/2016 893 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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ