Report to list % of tickets closed within 24 hours of being created
I am needing a report that will show tickets with a specific title and the % of those tickets that have been closed within 24 hours of being created for the previous quarter only. The tickets are part of a process so the title of the tickets will always be the same.
Thank you.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Krexin
9 years ago
This worked for me. It results in a report showing the last 90 days, and each column is divided by the total tickets for the queue. You can change the interval to anything you want. 180 for six months and so on. I run it from MySQL Workbench. After you run it, export it to Excel, then format fields for percentages.
If you want to use it within KACE you need to remove the SET variable at the top and define @days as 90 through the script.
Good luck.
SET @days = 180;Select a.QueueName as 'Queue Name', (c.4Tickets/b.AllTickets) as 'Within 4 Hours', (d.8Tickets/b.AllTickets) as '4 to 8 Hours', (e.24Tickets/b.AllTickets) as '8 to 24 Hours', (f.48Tickets/b.AllTickets) as '24 to 48 Hours', (g.72Tickets/b.AllTickets) as '48 to 72 Hours', (h.1WeekTickets/b.AllTickets) as '72 Hours to 1 Week', (i.RemainingTickets/b.AllTickets) as 'Greater than 1 Week', b.AllTickets as 'Total Tickets'from-- Select Queues(select HQ.ID, HQ.NAME as QueueNameFROM HD_QUEUE HQWHERE HQ.NAME != 'Project' AND HQ.NAME != 'Change Control' AND HQ.NAME != 'Help Desk (obsolete)' AND HQ.NAME != 'Development' AND HQ.NAME != 'Accela Change Control') aleft join-- All tickets during period(select HD_QUEUE_ID, count(HD_TICKET.ID) as AllTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY))GROUP BY HD_QUEUE_ID) bon a.ID = b.HD_QUEUE_IDleft join-- Tickets closed within 4 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 4TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 0 and 8)GROUP BY HD_QUEUE_ID) con a.ID = c.HD_QUEUE_IDleft join-- Tickets closed within 8 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 8TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 0 and 8)GROUP BY HD_QUEUE_ID) don a.ID = d.HD_QUEUE_IDleft join-- Tickets closed within 24 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 24TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 8 and 24)GROUP BY HD_QUEUE_ID) eon a.ID = e.HD_QUEUE_IDleft join-- Tickets closed between 24 and 48 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 48TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 24 and 48)GROUP BY HD_QUEUE_ID) fon a.ID = f.HD_QUEUE_IDleft join-- Tickets closed between 48 and 72 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 72TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 48 and 72)GROUP BY HD_QUEUE_ID) gon a.ID = g.HD_QUEUE_IDleft join-- Tickets closed between 72 hours and 1 week(select HD_QUEUE_ID, count(HD_TICKET.ID) as 1WeekTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 72 and 168)GROUP BY HD_QUEUE_ID) hon a.ID = h.HD_QUEUE_IDleft join-- Tickets closed greater than 1 week(select HD_QUEUE_ID, count(HD_TICKET.ID) as RemainingTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 > 168)GROUP BY HD_QUEUE_ID) ion a.ID = i.HD_QUEUE_IDOrder By a.QueueName;
