MSSQL-GROUP BY Date语句显示错误的数据

Question

我有一个GROUP BY语句，它根据周对数据进行排序。这里的问题是我的数据没有被计算整个星期(星期天不计算在内(

这里有一个例子：

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

这里是我的Sql查询，它在2周内重试上面的数据分割，而这应该是一周的计数：

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

结果：

|Count|
|  5  |
|  1  |

预期结果：

|Count|
|  6  |

Answer 1

似乎您想要ISO_WEEK:

SELECT COUNT(DISTINCT ProductNR) AS [Count]
FROM Products
WHERE YEAR (CREATED) = '2018'
GROUP BY DATEPART(ISO_WEEK, created), YEAR(created); 

Answer 2

SELECT 
count(Distinct ProductNR) AS [Count]
FROM Products
WHERE
CREATED >= '2018-01-01' and CREATED < '2019-01-01'
GROUP BY 
datediff(d, 0, created) / 7

如果你想包括本周，这里有一个完整的脚本，包括iso年和iso周

DECLARE @year DATE = '2018'
SELECT 
count(Distinct ProductNR) AS [Count], 
datepart(yy, dateadd(wk, datediff(d, 0, CREATED)/7, 3)) year,
datepart(iso_week, CREATED) week
FROM Products
WHERE
CREATED >= dateadd(wk, datediff(d, 0, dateadd(d, 3, @year))/7, 0)
and CREATED < dateadd(wk, datediff(d, 0, dateadd(d, 3, dateadd(yy, 1, @year)))/7, 0)
GROUP BY 
datepart(yy, dateadd(wk, datediff(d, 0, CREATED)/7, 3)),
datepart(iso_week, CREATED)

Answer 3

问题是有些地方在周日开始一周，所以我认为sql将2018-07-22作为下周的一部分

您可以尝试使用ISO_WEEK

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

Answer 4

只需使用以下查询将星期一设置为一周中的第一天：设置日期优先1
@MishMish这是我使用的代码片段

<!-- language: sql -->
USE tempdb
IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Products')
DROP TABLE Products
CREATE TABLE [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
--SELECT  datepart(wk, Date) as Week, * FROM Products
--Count Before Setting First Date To Monday
SET DATEFIRST 7  -- Sunday ( default, U.S. English)
SELECT count(Distinct ProductNR) AS [Count]
FROM Products
WHERE
YEAR (Date) = '2018'
Group by datepart(wk, Date), year(Date) 
--CHANGE FIRST Date To Monday
SET DATEFIRST 1
--Count AFTER Setting First Date To Monday
SELECT count(Distinct ProductNR) AS [Count]
FROM Products
WHERE
YEAR (Date) = '2018'
Group by datepart(wk, Date), year(Date)