How to get data for missing weeks in Summarised data

I have two tables stores_data and financial_week as shown below. Stores data is a summarised data across multiple attributes. My task is to generate data for all the weeks present in the second table, if data is missing, the quantity should be listed as 0.

create table stores_data
(
    store string
    ,supplier string
    ,category string
    ,qty int
    ,financial_week int
);

create table financial_week(
    financial_week int
);

insert into stores_data values('The Ponds','Bega','Snacks',50,20211);
insert into stores_data values('The Ponds','Bega','Drinks',50,20211);
insert into stores_data values('The Ponds','Bega','Snacks',100,20212);
insert into stores_data values('Strathfield','Bega','Snacks',50,20213);
insert into stores_data values('Parramatta','Bega','Drinks',110,20214);


insert into financial_week values(20211);
insert into financial_week values(20212);
insert into financial_week values(20213);
insert into financial_week values(20214);

​x
 
create table stores_data(    store string    ,supplier string    ,category string    ,qty int    ,financial_week int);​create table financial_week(    financial_week int);​insert into stores_data values('The Ponds','Bega','Snacks',50,20211);insert into stores_data values('The Ponds','Bega','Drinks',50,20211);insert into stores_data values('The Ponds','Bega','Snacks',100,20212);insert into stores_data values('Strathfield','Bega','Snacks',50,20213);insert into stores_data values('Parramatta','Bega','Drinks',110,20214);​​insert into financial_week values(20211);insert into financial_week values(20212);insert into financial_week values(20213);insert into financial_week values(20214);​

Expected Result set is this –

I have done cross join but after that I am not clearly sure how to proceed with getting missing weeks.

Here is the query I used so far but not seeing the null values to which I can put a logic in –

select *
from (
    select sd.*
    from stores_data sd
        cross join financial_week fw
)inr
    left join stores_data sd
        on sd.financial_week = inr.financial_week
order by inr.store,inr.supplier,inr.category,inr.financial_week

 
select *from (    select sd.*    from stores_data sd        cross join financial_week fw)inr    left join stores_data sd        on sd.financial_week = inr.financial_weekorder by inr.store,inr.supplier,inr.category,inr.financial_week​

Answer

Use a cross join to generate the rows and then left join to bring in the values:

select ssc.store, ssc.supplier, ssc.category, fw.financial_week,
       coalesce(sd.quantity, 0)
from (select distinct store, supplier, category
      from stores_data
     ) ssc cross join
     financial_week fw left join
     stores_data sd
     on sd.store = ssc.store and sd.supplier = ssc.supplier and
        sd.category = ssc.category and
        sd.financial_week = fw.financial_week
order by ssc.store, ssc.supplier, ssc.category, fw.financial_week;

 
select ssc.store, ssc.supplier, ssc.category, fw.financial_week,       coalesce(sd.quantity, 0)from (select distinct store, supplier, category      from stores_data     ) ssc cross join     financial_week fw left join     stores_data sd     on sd.store = ssc.store and sd.supplier = ssc.supplier and        sd.category = ssc.category and        sd.financial_week = fw.financial_weekorder by ssc.store, ssc.supplier, ssc.category, fw.financial_week;​

Note that the first part of the cross join generates all (distinct) combinations of store, supplier, and category. The final join conditions use all the columns from the cross joined tables.

Advertisement

Answer