如何在MS SQL Server中插入临时表中缺失的年份

gtlvzcf8  于 2022-12-10  发布在  SQL Server
关注(0)|答案(2)|浏览(129)

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 |

xpcnnkqh

xpcnnkqh1#

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);
oogrdqng

oogrdqng2#

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.

相关问题