I have data in table like this.

I want to fill/insert the rows with missing quarters from 2019-Q1 to 2022-Q4 with Sales as 0 for missing quarters and rest of the dimensions as it is.

For example: West has only two rows of quarter 2019-Q1 AND 2020-Q3. I also need other 14 rows of remaining quarters between 2019-Q1 AND 2022-Q4.

Sample fiddle data set: Data Set

Can some please help with sql code to achieve the same ?

CodePudding user response：

Try this,

CREATE TABLE #Fill_Gaps
    (Customer_Key int, Customer_Name varchar(14), Component varchar(6), Region varchar(5),Quarter varchar(7), Sales varchar(15))
;
    
INSERT INTO #Fill_Gaps
    (Customer_Key, Customer_Name, Component, Region, Quarter, Sales)
VALUES
    (109, 'General Motors', 'Piston', 'EAST', '2019-Q3', '7,85,063.37'),
    (109, 'General Motors', 'Piston', 'EAST', '2020-Q2', '24,36,808.92'),
    (109, 'General Motors', 'Piston', 'EAST', '2020-Q4', '19,03,820.10'),
    (109, 'General Motors', 'Piston', 'EAST', '2021-Q2', '62,24,228.01'),
    (109, 'General Motors', 'Piston', 'EAST', '2022-Q3', '44,35,135.25'),
    (109, 'General Motors', 'Piston', 'NORTH', '2020-Q2', '0'),
    (109, 'General Motors', 'Piston', 'NORTH', '2020-Q3', '-1,93,84,747.20'),
    (109, 'General Motors', 'Piston', 'NORTH', '2021-Q2', '-4,78,786.77'),
    (109, 'General Motors', 'Piston', 'NORTH', '2021-Q3', '3,84,93,986.44'),
    (109, 'General Motors', 'Piston', 'WEST', '2019-Q1', '10,29,581.28'),
    (109, 'General Motors', 'Piston', 'WEST', '2020-Q3', '5,51,29,226.88')
;

CREATE TABLE #Quarters
    (Quarters varchar(7))
;
    
INSERT INTO #Quarters
    (Quarters)
VALUES
    ('2019-Q1'),
    ('2019-Q2'),
    ('2019-Q3'),
    ('2019-Q4'),
    ('2020-Q1'),
    ('2020-Q2'),
    ('2020-Q3'),
    ('2020-Q4'),
    ('2021-Q1'),
    ('2021-Q2'),
    ('2021-Q3'),
    ('2021-Q4'),
    ('2022-Q1'),
    ('2022-Q2'),
    ('2022-Q3'),
    ('2022-Q4')
;

create table #temp (Customer_Key int, Customer_Name varchar(14), Component varchar(6), Region varchar(5),Quarter varchar(7))

--insert into #temp(Customer_Key,Customer_Name, Component, Region,Quarter)
--Select distinct Customer_Key,Customer_Name, Component, Region,Quarters
--from #Fill_Gaps A, #Quarters B  

 ---OR
 insert into #temp(Customer_Key,Region,Quarter)
Select distinct Customer_Key,Region,Quarters
from #Fill_Gaps A, #Quarters B

update t
set Customer_Name=fg.Customer_Name,Component=fg.Component
from  #temp t
inner join #Fill_Gaps fg on fg.Customer_Key=t.Customer_Key

select t.Customer_Key , t.Customer_Name, t.Component , t.Region ,t.Quarter,isnull(fg.Sales,0)Sales 
from #temp t
left join #Fill_Gaps fg on fg.Customer_Key=t.Customer_Key and t.Quarter=fg.Quarter and t.Region=fg.Region

drop table #Fill_Gaps,#Quarters,#temp