Counting Weeks by Monday using DATEPART-CodePudding

I am trying to track orders per week using this simple query, where the week starts on Monday. The issue is, while the count is correct, all the weeks are one up (week 1 is displayed as 2 etc)

SET DATEFIRST 1;
SELECT DATEPART(yyyy,[ORDER-DATE]) as Year, DATEPART(wk, [ORDER-DATE]) as Week, count(*) as Count from PAYMENTS
    where [ORDER-DATE] >= '2022-01-03' and [ORDER-DATE] <= '2022-01-31' 
    group by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE])
    order by DATEPART(yyyy,[ORDER-DATE]), DATEPART(wk, [ORDER-DATE]);

The output looks like this:

Year    Week  Count
2022    2     25
2022    3     15
2022    4     19
2022    5     31

Now, the first week count is correct (25 payments between January 3rd and January 9th, 2022). However it's showing that as week 2. Weirdly enough, if I run the same query for 2019 (start date being 2019-12-30) it actually works as expected and week 1 is displayed and accurate!

If I change the first queries date to start on "2022-01-01" instead of "2022-01-03", it shows week 1 as only being January 1st and January 2nd (weekend).

EDIT:

The desired result is like follows:

Year    Week  Count
2022    1     25
2022    2     15
2022    3     19
2022    4     31

CodePudding user response：

Year 2022 started on a Saturday. SQL Server DATEPART() always treats January 1 as week 1. With SET DATEFIRST 1, Monday 2022-01-03 becomes the start of week 2.

If you don't care about weekends, perhaps you should redefine your weeks as Saturday through Friday. This can be done with SET DATEFIRST 6.

See this db<>fiddle.