'Counting Weeks by Monday using DATEPART
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
