SQL Report Request: Average tickets closed per day for days with closed tickets
Hello fellow KACE administrators!
My SQL knowledge is in it's infancy stages right now and so I am seeking help the best, the ITNINJA crowd. I am looking for a report that will take all the tickets that have been closed over the course of a month and display the average number of tickets that have been closed per day, for ONLY days that that had closed tickets.
We want to see a metric of the average number of tickets closed per work day. Most of the time weekends don't have any closed tickets, but if there is an outage somewhere our on-call fellow will make a ticket so excluding all weekends isn't possible. One way I could image the report looking would be to have a row that shows total closed tickets, another row showing how many days in the past month that had at least one closed ticket, then a final row that shows the average closed tickets per day. Not looking for any information for each ticket, just each row that shows a number (count). Thank you!
2 Comments
[ + ] Show comments
-
Anyone? - MAXintosh 8 years ago
-
Is the desire to show an average for only the days with closed tickets an attempt to only report on working days? - Hobbsy 8 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
MAXintosh
8 years ago
Top Answer
@Hobbsy,
Exactly. I wanted to know if there was an easier way to accomplish that than the 'almost accurate' way I created. With much testing I was able to create a SQL report that does what we want, but would like to see if we can get it more accurate. I will layout what I did verbally and then post the code for all ninjas to enjoy.
Desired Result: Out of an interval, what was the average number of tickets closed per day counting ONLY days that had ticket(s) closed.
Approach: I don't know how to count days with tickets closed, but I do know that we normally only work M-F. So I took an interval (1 month) and used an algorithm posted on stackoverflow.com that calculates weekdays and made a count. Made a count for all closed tickets, and then divided the two which provided a rough estimate. While we MAY occasionally have a day without closing a ticket, the result should be about 90%-97% accurate.
Code:
SELECT Metric, TotalsFROM ((SELECT 1 AS ORD, 'Tickets Created' AS Metric, COUNT(HD_TICKET_CHANGE.ID) AS Totals FROM HD_TICKET_CHANGE INNER JOIN HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID WHERE description LIKE 'Ticket Created%' AND timestamp >= (CURDATE() - INTERVAL 1 MONTH)) UNION (SELECT 2 AS ORD, 'Tickets Closed' AS Metric, COUNT(HD_TICKET.ID) AS Totals FROM HD_TICKET WHERE TIME_CLOSED >= (CURDATE() - INTERVAL 1 MONTH)) UNION (SELECT 3 AS ORD, 'Number of Weekdays:' AS Metric, 5 * (DATEDIFF(CURDATE(), (CURDATE() - INTERVAL 1 MONTH)) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY((CURDATE() - INTERVAL 1 MONTH)) + WEEKDAY(CURDATE()) + 1, 1) AS Totals ) UNION (SELECT 4 AS ORD, 'AVG Created Per Day:' AS Metric, ((SELECT COUNT(*) FROM HD_TICKET_CHANGE INNER JOIN HD_TICKET ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID WHERE description LIKE 'Ticket Created%' AND timestamp >= (CURDATE() - INTERVAL 1 MONTH)) / (5 * (DATEDIFF(CURDATE(), (CURDATE() - INTERVAL 1 MONTH)) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY((CURDATE() - INTERVAL 1 MONTH)) + WEEKDAY(CURDATE()) + 1, 1))) AS Totals ) UNION (SELECT 5 AS ORD, 'AVG Closed Per Day:' AS Metric, ((SELECT COUNT(*) FROM HD_TICKET WHERE TIME_CLOSED >= (CURDATE() - INTERVAL 1 MONTH)) / (5 * (DATEDIFF(CURDATE(), (CURDATE() - INTERVAL 1 MONTH)) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY((CURDATE() - INTERVAL 1 MONTH)) + WEEKDAY(CURDATE()) + 1, 1))) AS Totals )) TMPORDER BY ORD;
Picture: