sp to populate a price table

[bittmapp]

i have a price table with product, priceDt, and price in it. the priceDt indicates the price of a product 'as of' that date. what i need to do is create a table with records for all of the products, for all of the dates from the products first priceDt up to today. in other words, the current table shows a new record only if the price has changed since the last priceDt. for example.


current table:

product priceDt price
--------------------------------------------
pen 01/01/2006 100
pen 01/06/2006 200

this means that the price of the pen was 100 from 01/01/06 through 01/05/2006, and i need to represent that in a new table. for example

new table:

product priceDt price
--------------------------------------------
pen 01/01/2006 100
pen 01/02/2006 100
pen 01/03/2006 100
pen 01/04/2006 100
pen 01/05/2006 100
pen 01/06/2006 200

thanks so much for your help,

bitt

 

[vongrunt]

<preachin'>Any number/calendar table handy?</preachin'>

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bittmapp]

Yes, I do have a time dimension built with calendar table .

bitt

 

[vongrunt]

OK. Suppose there is calendar table like this one:

create table Calendar( calDate smalldatetime primary key )
insert into Calendar values ( '20000101' )
while @@rowcount < 2048
	insert into Calendar 
		select calDate + (select count(*) from Calendar) from Calendar

select min(calDate), max(calDate) from Calendar

And your source table is:

create table srcData ( product varchar(8), priceDT smalldatetime, price smallmoney )
insert into srcData values('pen',  '01/01/2006', 100)
insert into srcData values('pen',  '01/06/2006', 200)

This self/theta-join will convert sequence of dates to intervals:

select A.product, A.priceDT, A.price, min(isnull(B.priceDT-1, A.priceDT))
from srcData A
left outer join srcData B on A.product=B.product and A.priceDT < B.PriceDT
group by A.product, A.priceDT, A.price

Now all you have to do is join on calendar table to resolve "missing" rows:

select X.product, C.calDate, X.price
from 
(	select A.product, A.priceDT, A.price, min(isnull(B.priceDT-1, A.priceDT)) as priceDT2
	from srcData A
	left outer join srcData B on A.product=B.product and A.priceDT < B.PriceDT
	group by A.product, A.priceDT, A.price
) X
inner join Calendar C on C.calDate between X.priceDT and X.priceDT2

Modify that query to do SELECT INTO or INSERT INTO... voila.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bittmapp]

Thank you so much for your reply! Will this work if the pen has additional price dates? for instance:

current table:

product priceDt price
--------------------------------------------
pen 01/01/2006 100
pen 01/06/2006 200
pen 01/10/2006 300


new table:

product priceDt price
--------------------------------------------
pen 01/01/2006 100
pen 01/02/2006 100
pen 01/03/2006 100
pen 01/04/2006 100
pen 01/05/2006 100
pen 01/06/2006 200
pen 01/07/2006 200
pen 01/08/2006 200
pen 01/09/2006 200
pen 01/10/2006 300
pen 01/11/2006 300
pen 01/12/2006 300
pen 01/13/2006 300
pen 01/14/2006 300
pen 01/15/2006 300
......until 03/10/2006 300


Thanks again, so much.

bitt

 

[vongrunt]

With that data - no, because there is no information when last price change (300) ends. But if you add:

product         priceDt         price
--------------------------------------------
pen             01/01/2006      100
pen             01/06/2006      200
pen             01/10/2006      300
[!]pen             03/10/2006      300[/!]

... I don't see any problem.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]