SQL - Closest Date match without going over
Posted on 2007-10-09
I have a Pricing file containing prices that has the following structure:
I have another file for Products that contains the following pertinent fields:
One product ID can have many price records. The Date_Purchased_Price needs to be filled in with the Price from the purchase price where the Pricing Date is closest to, without be later than, the Date_purchased.
For example, if the pricing table has the following
Product ID Date Price
234 1/1/2002 1.56
234 3/15/2006 1.75
234 4/10/2006 1.83
234 6/25/2006 1.90
234 7/20/2006 2.25
and the product 234 has a date_purchased of 5/15/2006, the Date_Purchased_Price should be filled with 1.83
I can do this within code, but I was wondering if there is a way to do this within SQL.
(We are using Oracle 9i)
Any ideas would be helpful