How to backfill data using SQL – populate missing values from historical data

Consider scenario where you want to populate missing data from previous date when data was available. In the following example we want to populate price of orders from most recent previous date when price was available.

create table #orderinfo
    odate datetime
   ,product varchar(100)
   ,price int 

insert into #orderinfo values('1/1/2010','Car',1000)
insert into #orderinfo values('1/2/2010','Car',null)
insert into #orderinfo values('1/3/2010','Car',null)

insert into #orderinfo values('1/1/2010','Toy',25)
insert into #orderinfo values('1/2/2010','Toy',35)
insert into #orderinfo values('1/3/2010','Toy',null)

insert into #orderinfo values('1/1/2010','Soda',1)
insert into #orderinfo values('1/2/2010','Soda',null)
insert into #orderinfo values('1/3/2010','Soda',3)

select * From #orderinfo

Update a  
set price=(
         select top 1 b.Price 
         from #orderinfo b 
         where b.odate < a.odate and b.product=a.product and b.price is not null 
         order by b.odate desc
From #orderinfo a
where a.price is null

select * From #orderinfo

drop table #orderinfo

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Tagged with: , ,
Posted in SQL Server, T-SQL Tips

Leave a Reply