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!

I am using SSMS. Not sure if that answers your question. Microsoft SQL Server Management System — Lefeba Gougis III, Dec 04 '21 at 22:41
How about using the week of a date? `datepart(week, DateCreated)`? *(depends on DATEFIRST setting)* Or joining to a [calendar table](https://stackoverflow.com/a/70218278/4003419)? — LukStorms, Dec 04 '21 at 23:02
Week5 has a typo: should be `AND DATEFROMPARTS(@year, @month, 31)` — Charlieface, Dec 05 '21 at 10:42

antonbijl · Answer 1 · 2021-12-04T23:31:04.193

I'm assuming:

that you want to start counting week 1 as starting on the first of the month and always being the first 7 days, and
week 5 should always be a partial week (i.e. the tail end of a month after the 28th), except in February of a non-leap year when it won't exist.
you want to see zero-weeks in the result set.

If all of these assumptions are true, I would use something like this:

DECLARE @Weeks TABLE (Week INT IDENTITY(1,1), SD DATETIME, ED DATETIME)
DECLARE @Date DATETIME = DATEFROMPARTS(@year,@month,1)
WHILE @Date < EOMONTH(DATEFROMPARTS(@year,@month,1))
BEGIN
    INSERT INTO @Weeks (SD, ED) 
        SELECT @Date, CASE WHEN DATEADD(DAY,7,@Date) > DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1)) THEN DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1)) ELSE DATEADD(DAY,7,@Date) END
    SET @Date = DATEADD(DAY,7,@Date)
END

SELECT w.Week, COUNT(ID) 'OrdersCount'
FROM @Weeks w 
    LEFT JOIN dbo.Orders ON w.SD <= DateCreated AND w.ED > DateCreated
GROUP BY w.Week

I've used a table variable to first build a list of weeks - this may not be strictly necessary (there are other ways to achieve this), but I like the flow. I recommend you use combination of < and start of next period as end date criteria, since BETWEEN is always inclusive and prone to issues with dates containing time portions in this kind of use case.

If you don't need to count weeks from the 1st day of the month in all cases and don't mind that zero-weeks won't be represented, then a really simple solution would be to use DATEPART(WEEK...), e.g.:

SELECT DATEPART(WEEK,DateCreated)-DATEPART(WEEK,DATEFROMPARTS(@year,@month,1))+1 'Week', COUNT(ID) 'CountID' 
    FROM dbo.Orders 
    WHERE DateCreated >= DATEFROMPARTS(@year,@month,1) AND DateCreated < DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1))
    GROUP BY DATEPART(WEEK,DateCreated)

Wow this produces pretty much exactly what I was looking for. That is a clever use of the 'WHILE'. I haven't really played around with loops in SQL so this is super informative, thank you! — Lefeba Gougis III, Dec 06 '21 at 18:01

score 0 · Accepted Answer · answered Dec 05 '21 at 10:42

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.

Thank you so much for the answer! I Have yet to come across 'CROSS APPLY (VALUES' so thank you for that. This is a cool way to approach the problem — Lefeba Gougis III, Dec 06 '21 at 17:47
You may find this post interesting https://stackoverflow.com/a/65818648/14868997 — Charlieface, Dec 06 '21 at 20:12

score 0 · Answer 3 · answered Dec 05 '21 at 17:08

This can be simplified - what you need to do is select 5 weeks always, then outer join to a table where you calculate the week for each row. Then it is a simple count of the number of rows in each week.

Note: this assumes week 1 is always the 1st through the 7th, which isn't how we normally calculate the weeks in a month. The iso_week starts on the Monday prior to the first Thursday of the month - and the US week 1 contains the 1st of the month through the first Saturday with the rest of the weeks starting on Sunday.

 --==== Some sample data
Declare @testData table (OrderId int, DateCreated datetime2);
 Insert Into @testData (OrderId, DateCreated)
 Values (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')
      , (1, '2021-11-04 06:01:14.6333333')
      , (2, '2021-11-09 07:01:14.6333333')
      , (3, '2021-11-18 00:00:00.0000000')
      , (4, '2021-11-28 06:01:14.6333333')
      , (5, '2021-11-30 06:01:14.6333333');

With the above sample data - we can do this:

 --==== Input parameters to select this year and this month
Declare @this_year int = 2021
      , @this_month int = 12;

 --==== Get the first of this month
Declare @start_month date = datefromparts(@this_year, @this_month, 1);

 --==== Force 5 weeks to be selected always (w.week_no) - left join to calculate week DateCreated falls into, join - group and count
 Select w.week_no
      , orders_count = count(t.week_no)
   From (Values (1), (2), (3), (4), (5))        As w(week_no)

   Left Join (Select week_no = datediff(day, @start_month, td.DateCreated) / 7 + 1
                From @testData                  As td
               Where td.DateCreated >= @start_month
                 And td.DateCreated <  dateadd(month, 1, @start_month)
              )                                 As t On t.week_no = w.week_no
  Group By
        w.week_no;

Or - we can use a CTE

 --==== Alternate method using CTE instead of derived table
   With order_weeks
     As (
 Select week_no = datediff(day, @start_month, td.DateCreated) / 7 + 1  
   From @testData                               As td
  Where td.DateCreated >= @start_month
    And td.DateCreated <  dateadd(month, 1, @start_month)
        )
 Select w.week_no
      , orders_count = count(ow.week_no)
   From (Values (1), (2), (3), (4), (5))        As w(week_no)

   Left Join order_weeks                        As ow On ow.week_no = w.week_no
  Group By
        w.week_no;

This assumes you will only be selecting data for a specific year/month combination. If this needs to be able to select for a range of years/months - then you need to add the years and months for all possible ranges and calculate the year/month/week for each date created.

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

3 Answers3