sql-server - 有没有办法填补没有 ReviewType 出现日期占位符和 0 次出现的日期空白

Question

有没有办法填补没有 ReviewType 出现日期占位符和 0 次出现的日期空白？

SELECT 
    dateCompleted, 
    Count(CASE ReviewType WHEN 1 THEN ReviewType END) AS NonPeak, 
    Count(CASE ReviewType WHEN 2 THEN ReviewType END) AS AM, 
    Count(CASE ReviewType WHEN 3 THEN ReviewType END) AS PM  
FROM ALAN.dbo.qryPeakReviews
WHERE ElementID Like '%SI%'
AND dateCompleted >= DATEADD(day, -Convert(Int, '900'), getdate())
GROUP BY dateCompleted

所需的输出（带有生成的行）是

dateCompleted   NonPeak AM  PM
6/5/2018              1  0   0
-- added
6/6/2018              0  0   0
6/7/2018              0  0   0
6/8/2018              0  0   0
6/9/2018              0  0   0
6/10/2018             0  0   0
-- generated
6/21/2018             2  0   0
8/27/2018             0  0   1
8/28/2018             0  1   0

标签： sql-servertsql

Answer 1

使用数字表格，您可以创建日期来填写表格中缺少的日期。在下面的查询中，我使用了 master.dbo.spt_values（限制为 2048）。但是，这是一个大部分未记录的表格，主要由 Microsoft 使用。最好创建自己的数字表，而不是依赖这个（参见这篇文章）。我在这里使用它只是为了传达如何使用这样的表格。

DECLARE @dtStart DATE
SET @dtStart = dateadd(day, -7, getdate())

SELECT
    tblA.dateCompleted,
    Count(CASE ReviewType WHEN 1 THEN ReviewType END) AS NonPeak,
    Count(CASE ReviewType WHEN 2 THEN ReviewType END) AS AM,
    Count(CASE ReviewType WHEN 3 THEN ReviewType END) AS PM
FROM (
    SELECT dateadd(day, number, @dtStart) as [dateCompleted]
    FROM (
        SELECT number 
        FROM master.dbo.spt_values
        WHERE [type] = 'P'
        ) tbl
    WHERE dateadd(day, number, @dtStart) >= @dtStart
    ) tblA
    LEFT JOIN ALAN.dbo.qryPeakReviews tblB ON tblA.dateCompleted = tblB.dateCompleted 
        and tblB.elementID Like '%SI%'
WHERE tblA.dateCompleted >= DATEADD(day, -7, getdate())
    AND tblA.dateCompleted < GETDATE()
GROUP BY tblA.dateCompleted

使用以下测试数据（注意缺少 1/4 和 1/6）...

create table #test (dateCompleted date, ReviewType int, elementID varchar(10))

insert into #test 
values ('2020-01-01', 1, 'SI')
insert into #test 
values ('2020-01-02', 2, 'SI')
insert into #test 
values ('2020-01-03', 3, 'SI')
insert into #test 
values ('2020-01-05', 1, 'SI')
insert into #test 
values ('2020-01-07', 1, 'SI')

产生以下输出（注意 1/4 和 1/6 包含在 0 值中）...

您可以在此处阅读有关 master.dbo.spt_values 表的更多信息