Fill Value For Each Month (T-SQL Sample)


declare @PriceList table (Tradedate datetime, Price numeric(30,2),ID VARCHAR(10)) 
insert into @PriceList select '1/31/2013',40.00,'id'

union all select '3/31/2013',50.00,'id' 

union all select '3/31/2013',80.00,'id2' 
union all SELECT '5/31/2013',90.00,'id3' 
SELECT * FROM @PriceList 



;with mycte as 
(Select *,row_number() Over(partition by ID order by TradeDate) rn,  dateadd(month,datediff(month, 0,Tradedate)+num,-1) as newEnd from @pricelist 
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(num)
)
,mycte1 as(
Select newEnd, Price, id,  row_number() Over(partition by ID,newEnd order by rn DESC) rn2 from mycte  WHERE newEnd<=(select max(Tradedate) from @pricelist)  
)

Select newEnd, Price, id from mycte1
WHERE rn2=1
order by id,newEnd
/*
newEnd	Price	id
2013-01-31 00:00:00.000	40.00	id
2013-02-28 00:00:00.000	40.00	id
2013-03-31 00:00:00.000	50.00	id
2013-04-30 00:00:00.000	50.00	id
2013-05-31 00:00:00.000	50.00	id
2013-03-31 00:00:00.000	80.00	id2
2013-04-30 00:00:00.000	80.00	id2
2013-05-31 00:00:00.000	80.00	id2
2013-05-31 00:00:00.000	90.00	id3

*/

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s