SQL Server Filling Missing Values in data

zpgglvta 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(122)

I have table and within that I have data like below (Similarly we have data for year 2020, 2021 and 2023). I require to fill missing periods in below data with values between 1 to 13 and this should been done by inserting dummy row for that period with amount as 0.

Now in below data period 2,5,6,10,11,12,14 are missing, so for each missing period I can get dummy record.

Year| Period  |Week |PeriodXweek     |amount
2022    1       2     1X2             10
2022    1       3     1X3             36
2022    3       4     3X4             20
2022    4       3     4X3             30
2022    7       3     4X3             40
2022    8       3     4X3             50
2022    9       3     4X3             60

Expected output

Year    Period  Week PeriodXweek     amount
2022    1       2     1X2             10
2022    1       3     1X3             36
2022    2       1     2X1             0**  Dummy Row
2022    3       4     3X4             20
2022    4       3     4X3             30
2022    5       1     5X1             0**  Dummy Row
2022    6       1     6X1             0**  Dummy Row
2022    7       3     4X3             40
2022    8       3     4X3             50
2022    9       3     4X3             60
2022    10      1     10X1            0
2022    11      1     11X1            0
2022    12      1     12X1            0
2022    13      1     13X1            0

I have tried using CTE and Left join but not able to manage above result.

sql-server

来源：https://stackoverflow.com/questions/76421681/filling-missing-values-in-data

1条答案

按热度按时间

7lrncoxx1#

A very brutish method is to gather all data that is missing by doing:

declare @t table (year int, period int, week int, periodxweek varchar(10) collate database_default, amount numeric(19,2))
insert into @t
select *
from (
    VALUES  (2022,1,2,'1X2',10)
    ,   (2022,1,3,'1X3',36)
    ,   (2022,3,4,'3X4',20)
    ,   (2022,4,3,'4X3',30)
    ,   (2022,7,3,'4X3',40)
    ,   (2022,8,3,'4X3',50)
    ,   (2022,9,3,'4X3',60)
) t (year, period, week,periodxweek,amount)

insert into @t
select t.year, x.period, 1, cast(x.period as varchar(30)) + 'X1', 0
from (
    select distinct year
    from @t t
    ) t
cross apply (
    values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)
    ) x(period)
where not exists(
    select  1
    from    @t t2
    where   t2.year = t.year
    and t2.period = x.period
    )

select *
from @t
order by year, period

Basically you get all possible year-period combinations and then test them for existing data with help of WHERE NOT EXISTS.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Filling Missing Values in data

1条答案

相关问题

热门标签

最新问答