Create record each day from time frame

microsoft.public.sqlserver.programming

12 Jan 2006 2:29 PM

AshleyT

I have a table that has cost records for a specific item for a specific start
date.

Start, cost, item, PromoCode, end date
01/02/2006, 2.45, 1234, R,
01/05/2006, 2.00, 1234, P, 01/08/2006
01/10/2006, 2.55, 1234, R,

If a record has a start date then that new cost begains. And if it doesn't
have an end date it will go indefinitely. Also when a promo (P) ends then
the cost goes back to the Regular (R) cost record.

So the cost records that I would create from the above records is

01/02/2006, 2.45, 1234, R
01/03/2006, 2.45, 1234, R
01/04/2006, 2.45, 1234, R
01/05/2006, 2.00, 1234, P
01/06/2006, 2.00, 1234, P
01/07/2006, 2.00, 1234, P
01/08/2006, 2.45, 1234, R
01/09/2006, 2.45, 1234, R
01/10/2006, 2.55, 1234, R
01/10/2006, 2.55, 1234, R
.......

Looking for any help with how to start a stored procedure or query to come
up with these records.

Thanks!

12 Jan 2006 3:09 PM

markc600

Suggest joining to an auxiliary calendar table

See http://www.aspfaq.com/2519