How to calculate a daily sales average based off the working days in a month

Question

We are attempting to get the average number of sales per month using SQL. We've been able to return the average number of sales for all of our data but not for specific months. Our current thinking is that by using the following code and dividing by the number of work days in a month, we should be able to get the results we are looking for. Any help or suggestions will be greatly appreciated.

By 'working days' we mean days where a sale came in. Example of what we're referring to is shown in the final code block.

Our current code looks as follows

SELECT
  AVG(Orders.num)
  /*Need Help Here*/
FROM 
(
  SELECT
    DAY(DateTimeCreated) as day,
    MONTH(DateTimeCreated) as month,
    YEAR(DateTimeCreated) as year,
    COUNT(DISTINCT OrderID) AS num
  FROM OrderHeader 
  WHERE
    DateTimeCreated >= DATEADD(
      month,
      datediff(month, 0, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
      0
    )  
    AND OrderType <> 2 
    AND Deleted <> 1 
    AND BranchID = 9
  GROUP BY
    YEAR(DateTimeCreated),
    MONTH(DateTimeCreated),
    DAY(DateTimeCreated)
)
AS Orders

Returns the Following

AVG Orders
48

Only Running the following

SELECT
  DAY(DateTimeCreated) as day,
  MONTH(DateTimeCreated) as month,
  YEAR(DateTimeCreated) as year,
  COUNT(DISTINCT OrderID) AS num
FROM OrderHeader 
WHERE
  DateTimeCreated >= DATEADD(
    month,
    datediff(month, 0, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
    0
  )  
  AND OrderType <> 2 
  AND Deleted <> 1 
  AND BranchID = 9
GROUP BY
  YEAR(DateTimeCreated),
  MONTH(DateTimeCreated),
  DAY(DateTimeCreated)
Order By
  YEAR(DateTimeCreated),
  MONTH(DateTimeCreated),
  DAY(DateTimeCreated)

Returns (Only showing 2 months as not to make this post longer than necessary)

day month   year    num
18  7   2023    22
19  7   2023    12
20  7   2023    37
21  7   2023    50
22  7   2023    18
23  7   2023    1
24  7   2023    56
25  7   2023    56
26  7   2023    74
27  7   2023    68
28  7   2023    41
30  7   2023    1
31  7   2023    55
1   8   2023    88
2   8   2023    62
3   8   2023    123
4   8   2023    91
5   8   2023    10
6   8   2023    4
7   8   2023    84
8   8   2023    77
9   8   2023    65
10  8   2023    56
11  8   2023    57
12  8   2023    5
13  8   2023    5
14  8   2023    78
15  8   2023    75
16  8   2023    53
17  8   2023    59
18  8   2023    51
19  8   2023    11
20  8   2023    24
21  8   2023    62
22  8   2023    59
23  8   2023    60
24  8   2023    92
25  8   2023    71
26  8   2023    1
27  8   2023    9
28  8   2023    63
29  8   2023    63
30  8   2023    72
31  8   2023    67

We have attempted too many different lines to state in one post. The closest we've came to accomplishing our desired result came from suggestions in the following post. How to determine the number of days in a month in SQL Server?. We believe the subquery path is the right way but are struggling on this final part.

Answer 1

Using a calendar table, this becomes somewhat trivial.

Using a randomized set of sales data and a calendar table function

SELECT c.Year, c.Month, CAST(DATEDIFF(DAY, c.MonthStartDate, c.MonthEndDate)+1.0 AS INT) AS MonthDays, COUNT(i.InvoiceID) AS Invoices, 
       CAST(COUNT(i.InvoiceID) / (DATEDIFF(DAY, c.MonthStartDate, c.MonthEndDate)+1.0) AS DECIMAL(10,2)) AS AvgInovicesPerDay
  FROM dbo.Calendar(2,0) c
    LEFT OUTER JOIN Invoices i
      ON i.InvoiceDateTimeUTC BETWEEN c.Date AND c.DateEndTime
 GROUP BY c.Year, c.Month, DATEDIFF(DAY, c.MonthStartDate, c.MonthEndDate)+1.0
 ORDER BY c.Year, c.Month;

Year	Month	MonthDays	Invoices	AvgInovicesPerDay
2022	1	31	4239	136.74
2022	2	28	3929	140.32
2022	3	31	4217	136.03
2022	4	30	4158	138.60
2022	5	31	4154	134.00
2022	6	30	4094	136.47
2022	7	31	4289	138.35
2022	8	31	4370	140.97
2022	9	30	4117	137.23
2022	10	31	4225	136.29
2022	11	30	4099	136.63
2022	12	31	4211	135.84
2023	1	31	4161	134.23
2023	2	28	3848	137.43
2023	3	31	4323	139.45
2023	4	30	4144	138.13
2023	5	31	3087	99.58
2023	6	30	10080	336.00
2023	7	31	620	20.00
2023	8	31	0	0.00
2023	9	30	0	0.00
2023	10	31	0	0.00
2023	11	30	0	0.00
2023	12	31	0	0.00
2024	1	31	0	0.00
2024	2	29	0	0.00

This gives us the total invoice count for the month, and the number of days in each month and an average per day.

This of course does not take into account weekdays, or public holidays and assumes all days count.

If we just want to include invoices made on week days, the calendar table also has a column we can filter on. Note that this will then also exclude any invoices which were created on week ends:

SELECT c.Year, c.Month, COUNT(DISTINCT c.Date) AS MonthDays, COUNT(i.InvoiceID) AS Invoices, 
       CAST(COUNT(i.InvoiceID) / (COUNT(DISTINCT c.Date)+.0) AS DECIMAL(10,2)) AS AvgInovicesPerDay
  FROM dbo.Calendar(2,0) c
    LEFT OUTER JOIN Invoices i
      ON i.InvoiceDateTimeUTC BETWEEN c.Date AND c.DateEndTime
 WHERE c.IsWeekDay = 1
 GROUP BY c.Year, c.Month
 ORDER BY c.Year, c.Month;

Year	Month	MonthDays	Invoices	AvgInovicesPerDay
2022	1	21	2876	136.95
2022	2	20	2825	141.25
2022	3	23	3114	135.39
2022	4	21	2899	138.05
2022	5	22	3008	136.73
2022	6	22	3032	137.82
2022	7	21	2840	135.24
2022	8	23	3210	139.57
2022	9	22	3026	137.55
2022	10	21	2854	135.90
2022	11	22	3056	138.91
2022	12	22	3002	136.45
2023	1	22	2971	135.05
2023	2	20	2773	138.65
2023	3	23	3235	140.65
2023	4	20	2746	137.30
2023	5	23	2274	98.87
2023	6	22	0	0.00
2023	7	21	620	29.52
2023	8	23	0	0.00
2023	9	21	0	0.00
2023	10	22	0	0.00
2023	11	22	0	0.00
2023	12	21	0	0.00
2024	1	23	0	0.00
2024	2	15	0	0.00

This now gives us a count of just the week days, and an average of the invoices from those days. You can see that the total for January 2022 is now significantly lower, indicating that in my data set there were a lot of invoices on the weekends. Interestingly the averages are not dissimilar.