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!
Cancel

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

### 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: