在不返回任何数据的情况下，按周在星期一转换0计数，SQL

Question

我正在尝试编写一个sql查询，它将返回每周开始的所有产品的计数。日期是每周一。因此，每一行都将返回该周的星期一，以及该周开始的单元总数。运行报告的当前季度中每周需要的总周数。我有这个代码，但是当那一周没有创建单元时，数据就会丢失。我需要在没有启动单元的周上返回0，而不是它只是不在那里。我在网上找到了一些资源，但他们大多使用"generate_series“命令，而我的SSMS似乎没有这个命令。

示例数据: tableA

Product--------DateStarted-----
1              2019-10-2
1              2019-10-4
1              2019-10-3
1              2019-10-23
1              2019-11-08
1              2019-11-09

-

期望的结果：

 Week----------Product Count
 2019-09-30    3
 2019-10-07    0
 2019-10-14    0
 2019-10-21    1
 .....Continued 0s for each week with the date being monday
 2019-11-04    2
 ...continued to end of quarter
 2019-12-30     0

-

当前结果：

  Week------------ProductCount-----
  2019-09-30      3
  2019-10-21      1
  2019-11-04      2

-

当前SQL代码

SELECT Count([week]) as 'TotalStarted', [week] FROM 
(SELECT 
DATEADD(DAY, 2 -DATEPART(WEEKDAY, Crated),  CAST(DateStarted as date)) as 'Week' 
FROM tableA 
WHERE Product like ('1') 
AND [DateStarted] >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) AND 
[DateStarted] <= DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))) tbla 
Group by [Week]

Answer 1

使用having

SELECT Count([week]) as 'TotalStarted', [week] FROM 
(SELECT 
DATEADD(DAY, 2 -DATEPART(WEEKDAY, Crated),  CAST(DateStarted as date)) as 'Week' 
FROM tableA 
WHERE Product like ('1') 
AND [DateStarted] >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) AND 
[DateStarted] <= DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))) tbla 
Group by [Week]
HAVING Count([week])>0

对不起，我真的看错了。试试这样的东西。事实上，这是avery_larry的答案

如果你认为你必须调整一周中有代表性的日子

;WITH [0 to 12] (v) as 
(
SELECT V
FROM (
  VALUES (0), (1), (2), (3), (4), (5) ,(6), (7), (8), (9), (11), (12) 
)T(V)
),
[date_in_qq] (week) as (
    SELECT DATEADD(day,DATEDIFF(day,0,DATEADD(qq, DATEDIFF(qq, 0, GETDATE()),0)),v*7)
    FROM [0 to 12]
)
SELECT Count(tbla.[week]) as 'TotalStarted', [date_in_qq].[week] FROM 
[date_in_qq] 
LEFT OUTER JOIN 
(SELECT 
DATEADD(DAY, 2 -DATEPART(WEEKDAY, Crated),  CAST(DateStarted as date)) as 'Week' 
FROM tableA 
WHERE Product like ('1') 
AND [DateStarted] >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) AND 
[DateStarted] <= DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))) 
tbla ON tbla.[week] = [date_in_qq].[week]
Group by [date_in_qq].[Week]