SQL - Closest Date match without going over

Posted on 2007-10-09
Last Modified: 2009-07-17
I have a Pricing file containing prices that has the following structure:
Product ID

I have another file for Products that contains the following pertinent fields:
Product ID
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
Question by:GNOVAK
    LVL 7

    Accepted Solution

    ...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 :)
    LVL 7

    Expert Comment

    of course, the first line of the query it should be:

    update Products p set Date_Purchased_Price=(


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Title # Comments Views Activity
    Need help creating a stored procedure 8 51
    What does this SQL mean? 7 31
    VB Access SQL question 2 16
    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now