Clash Royale CLAN TAG

MSSQL - GROUP BY Date statement showing wrong data

I have a GROUP BY statement which sort my data based on weeks. The problem here is my data are not being counted for the whole week (Sundays are not counted in)

Here is an example:

CREATE TABLE [dbo].[Products](
[ProductNR] [varchar](14) NULL,
[Location] [int] NULL,
[Date] [datetime] NULL);

INSERT INTO Products (ProductNR, Location, Date)
VALUES
('12345678911' ,1, '2018-07-16 00:00:00.000'), -- Monday
('12345678912' ,1, '2018-07-16 00:00:00.000'), -- Monday
('12345678913' ,1, '2018-07-16 00:00:00.000'), -- Monday
('12345678914' ,1, '2018-07-16 00:00:00.000'), -- Monday
('12345678915' ,2, '2018-07-16 00:00:00.000'), -- Monday
('12345678916' ,3, '2018-07-22 00:00:00.000'); -- This is a sunday


And here is my Sql query in which it retrives the above data split for on 2 weeks when it supposed to be the count for just one week:

SELECT count(Distinct ProductNR) AS [Count]
FROM Products
WHERE
YEAR (CREATED) = '2018'
Group by datepart(wk, created), year(created)


Result:

|Count|
| 5 |
| 1 |


Expected result:

|Count|
| 6 |


1 Answer
1

The thing is some places start the week on sunday so i think sql counts 2018-07-22 as part of the next week

2018-07-22

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.