How to insert missing years in temporary table in MS SQL Server-CodePudding

I work with Sales and problem is that this table does not have records for each client for every year. Records are missing randomly. Instead i need to have those years there and put 0 for sales for those years for my analysis.

I have limited knowledge of SQL. Can anybody help on this one? What i have as of now and what i would like to have is shown below.

I have thoughts to use LAG() function, but missing records can be for 2 years in a row or 3. I am not sure how to tackle such problem.

What I have now:

Client_ID	SalesYear	Sales
1	2010	12
1	2012	20
1	2013	21
1	2016	14

What i need to have:

Client_ID	SalesYear	Sales
1	2010	12
1	2011	0
1	2012	20
1	2013	21
1	2014	0
1	2015	0
1	2016	14

CodePudding user response：

You need a complete list of years to outer-join with.

You can do this a number of ways, the basic principle would be:

with y as (
  select * from (values(2010),(2011),(2012),(2013),(2014),(2015),(2016))y(y)
)
insert into t (Client_Id, SalesYear, Sales)
select 1, y.y, 0
from y
where not exists (select * from t where t.SalesYear = y.y);

CodePudding user response：

Something like this might help:

DECLARE @Sales TABLE
(Client_ID int, SalesYear int, Sales money)

INSERT INTO @Sales(Client_ID, SalesYear, Sales) SELECT 1, 2010, 12 
INSERT INTO @Sales(Client_ID, SalesYear, Sales) SELECT 1, 2012, 20 
INSERT INTO @Sales(Client_ID, SalesYear, Sales) SELECT 1, 2013, 21 
INSERT INTO @Sales(Client_ID, SalesYear, Sales) SELECT 1, 2016, 14;



with years as 
(
    select 2000 as theYear 
    UNION ALL
    select y.theYear   1 as theYear
    from years y
    where y.theYear   1 <= YEAR(GetDate())
)

select 
    Y.theYear, S.Client_ID, S.Sales
FROM 
    Years Y
LEFT JOIN
    @Sales S ON S.SalesYear = Y.theYear
option (maxrecursion 0)

You can change "2000" to something more appropriate.