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

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