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! 
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Anyone?
  • Is the desire to show an average for only the days with closed tickets an attempt to only report on working days?
Please log in to comment

Answer Chosen by the Author

1
@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:

Answered 12/29/2015 by: MAXintosh
Senior Purple Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity