SQL - Closest Date match without going over

I have a Pricing file containing prices that has the following structure:
Price
Date
Product ID

I have another file for Products that contains the following pertinent fields:
Product ID
Date_purchased
Date_Purchased_Price (blank)

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
GNOVAKAsked:
Who is Participating?
 
DiscoNovaCommented:
...umm... something like:

update Products p set Price=(
  select Price from PricingTable where ProductId=p.ProductId and Date=(
    select max(Date) from PricingTable where ProductId=p.ProductId and Date <= p.Date_purchased
  )
) where Date_Purchased_Price is null; -- This is just to not overwrite something that has already been set :)
0
 
DiscoNovaCommented:
of course, the first line of the query it should be:

update Products p set Date_Purchased_Price=(

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.