Binaryworld - How to backfill data (i.e. populate missing values from historical dat ... [ SQL Server -> T-SQL ]

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.

Click here to copy the following block

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

--//backfill
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
go