![\"\"](\"\/Main\/Articles\/Lang6\/Cat109\/Code4121\/backfill_data.jpg\")
<\/p>\nConsider 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.<\/p>\n
<\/p>\n
create table #orderinfo\r\n(\r\n odate datetime\r\n ,product varchar(100)\r\n ,price int \r\n)\r\n\r\ninsert into #orderinfo values('1\/1\/2010','Car',1000)\r\ninsert into #orderinfo values('1\/2\/2010','Car',null)\r\ninsert into #orderinfo values('1\/3\/2010','Car',null)\r\n\r\ninsert into #orderinfo values('1\/1\/2010','Toy',25)\r\ninsert into #orderinfo values('1\/2\/2010','Toy',35)\r\ninsert into #orderinfo values('1\/3\/2010','Toy',null)\r\n\r\ninsert into #orderinfo values('1\/1\/2010','Soda',1)\r\ninsert into #orderinfo values('1\/2\/2010','Soda',null)\r\ninsert into #orderinfo values('1\/3\/2010','Soda',3)\r\n\r\nselect * From #orderinfo\r\n\r\n--\/\/backfill\r\nUpdate a \r\nset price=(\r\n select top 1 b.Price \r\n from #orderinfo b \r\n where b.odate < a.odate and b.product=a.product and b.price is not null \r\n order by b.odate desc\r\n )\r\nFrom #orderinfo a\r\nwhere a.price is null\r\n\r\nselect * From #orderinfo\r\n\r\ndrop table #orderinfo\r\ngo<\/pre>\n","protected":false},"excerpt":{"rendered":"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 ( …<\/p>\n