?
Solved

SQL - Closest Date match without going over

Posted on 2007-10-09
2
Medium Priority
?
1,728 Views
Last Modified: 2009-07-17
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
0
Comment
Question by:GNOVAK
  • 2
2 Comments
 
LVL 7

Accepted Solution

by:
DiscoNova earned 1500 total points
ID: 20044374
...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
 
LVL 7

Expert Comment

by:DiscoNova
ID: 20044380
of course, the first line of the query it should be:

update Products p set Date_Purchased_Price=(

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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