SQL : given a year and month how can I count the number of orders per WEEK

Question

I have been tasked with returning the number of orders 'per week' within a given year and month (this is all happening within SSMS).

My data looks like something this:

OrderId	DateCreated
1	2021-12-04 06:01:14.6333333
2	2021-12-04 07:01:14.6333333
3	2021-12-24 00:00:00.0000000
4	2021-12-31 06:01:14.6333333
5	2021-12-31 06:01:14.6333333

I would like to get the results table to look something like this:

Week	OrdersCount
1	1
2	0
3	0
4	1
5	2

Currently I have the following SQL stored procedure that takes in a year (@year) and month (@month) as parameters:

SELECT
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 01)) AND (DATEFROMPARTS(@year, @month, 07)) 
             THEN 1 ELSE 0 END) AS Week1,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 08)) AND (DATEFROMPARTS(@year, @month, 14)) 
             THEN 1 ELSE 0 END) AS Week2,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 15)) AND (DATEFROMPARTS(@year, @month, 21)) 
             THEN 1 ELSE 0 END) AS Week3,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 22)) AND (DATEFROMPARTS(@year, @month, 28)) 
             THEN 1 ELSE 0 END) AS Week4,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 29)) AND (DATEFROMPARTS(@year, @month, 29)) 
             THEN 1 ELSE 0 END) AS Week5
FROM
    dbo.Orders

The above statement returns something that is close-ish to what I need but there are some issues, my result set looks like this:

	wk1	wk2	wk3	wk4	wk5
1	1	0	0	1	0

So the biggest issues are of course the orientation and missing orders in week 5. My weeks are displayed along the x-axis instead of the y-axis, but also it seems that since the EOMONTH() function defaults the time stamp to midnight, any orders placed past 12am on the last day of the month are not taken into account.

Based on the research I have done thus far, I believe that I should be using some combination of DATEADD, DATEDIFF and COUNT (as opposed to SUM, so that I can do a GROUP BY) I have a good understanding of how these functions/statements work independently but am having trouble bringing them together to reach my goal. Any and all help would be much appreciated!

Answer 1

You can use CASE to get a single week number then group by that number.

Use CROSS APPLY (VALUES to avoid repeating code

SELECT
    v.WeekNumber,
    TotalOrders = COUNT(*)
FROM
    dbo.Orders
CROSS APPLY (VALUES (
    CASE WHEN DateCreated >= DATEFROMPARTS(@year, @month, 1) AND DateCreated < DATEFROMPARTS(@year, @month, 8)
         THEN 1
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 8) AND DateCreated < DATEFROMPARTS(@year, @month, 15)
         THEN 2
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 15)) AND DateCreated < DATEFROMPARTS(@year, @month, 22)
         THEN 3
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 22)) AND DateCreated < DATEFROMPARTS(@year, @month, 29)
         THEN 4
         ELSE 5
    END
)) v(WeekNumber)

WHERE DateCreated >= DATEFROMPARTS(@year, @month, 1)
  AND DateCreated < DATEADD(month, 1, DATEFROMPARTS(@year, @month, 1))
GROUP BY
  v.WeekNumber;

Note the use of >= AND < for a half-open interval. This ensure that the whole of the last day is included.