Generate dummy values if the Column data (X,Y,Z) is absent for a date

August 7, 2023

I have a table A as :

and I need to generate output like below:

So, whenever x,y,z column values for "CODE" is missing for a particular date, we need to generate dummy rows with 0 values as showing in the output.

>Solution :

declare @temp table(id int IDENTITY(1,1), rowDate date, code varchar(1), val int);
insert into @temp(rowdate, code, val)values('01.08.2023', 'x', 100);
insert into @temp(rowdate, code, val)values('01.08.2023', 'y', 100);
insert into @temp(rowdate, code, val)values('01.08.2023', 'z', 100);
insert into @temp(rowdate, code, val)values('01.08.2023', 'a', 100);
insert into @temp(rowdate, code, val)values('01.08.2023', 'b', 100);
insert into @temp(rowdate, code, val)values('01.08.2023', 'c', 100);
insert into @temp(rowdate, code, val)values('02.08.2023', 'x', 100);
insert into @temp(rowdate, code, val)values('02.08.2023', 'y', 100);
insert into @temp(rowdate, code, val)values('02.08.2023', 'z', 100);


select 
tempTable.rowDate, 
tempTable.code, 
COALESCE(origTable.val, tempTable.val) as val 
from
    (select 
    a.rowDate, 
    b.code, 
    0 as val
    from (select distinct rowdate from @temp) as a 
    inner join (select distinct code from @temp) as b 
    on 1=1) 
    as tempTable
left join @temp as origTable
on tempTable.rowDate = origTable.rowDate 
and tempTable.code = origTable.code
order by tempTable.rowDate, tempTable.code