sql-server - 在没有返回数据的情况下，每周的星期一投 0 计数，SQL

Question

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

示例数据：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]

标签： sql-serverdatecastingcount

Answer 1

使用有

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]