Is there a way to fill the date gap where no ReviewType occurred with a date place holder and 0 occurrences

Question

Is there a way to fill the date gap where no ReviewType occurred with a date place holder and 0 occurrences?

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

required output (with generated rows) is

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

Good day SAL, Please provide queries to create a sample table and to insert several sample rows. Next please explain what is the requested result according to the sample data. This will help us to reproduce the issue fast and will provide better understanding of what you are looking for. — Ronen Ariely, Jan 07 '20 at 23:34
In addition it is recommended to add the tag "T-SQL" since this question is regarding T-SQL query — Ronen Ariely, Jan 07 '20 at 23:34
Aside: `-Convert(Int, '900')`? Not `(-(Convert(Int, ('900'))))`? — HABO, Jan 08 '20 at 00:21

jfarleyx · Answer 1 · 2020-01-08T01:01:17.927

Using a numbers table you can create dates to fill in the dates missing from your table. In the query below, I use the master.dbo.spt_values (which is limited to 2048). However, this is a largely undocumented table and is primarily used by Microsoft. It's probably best to create your own numbers table and not rely on this one (see this post). I use it here just to convey how a table like this can be used.

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

Using the following test data (note that 1/4 & 1/6 are missing)...

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')

Produced the following output (note that 1/4 & 1/6 are included with 0 values)...

You can read more about master.dbo.spt_values table here

Is there a way to fill the date gap where no ReviewType occurred with a date place holder and 0 occurrences

1 Answers1