Match date with price 1

[huggybear]

I have 2 tables, Price (Date, Price, SiteID) and Transaction (Account, Date, Site, Quantity). Price is updated periodically as changes occur, could be many days between changes. Transactions can occur many times a day.

I need a query to match the transaction with the price for the date and site of the transaction, grouped by account then site.

I'm stuck getting the right price for the right date since there may not be a matching date in the price table. Can anyone suggest a solution?

Thanks, Bear

 

[PHV]

A starting point:
SELECT T.Account, T.Site, T.Date, P.Date, P.Price, T.Quantity
FROM Transaction AS T INNER JOIN Price AS P ON T.Site = P.SiteID
WHERE P.Date = (SELECT Max([Date]) FROM Price WHERE SiteID=T.Site AND [Date]<=T.Date);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[huggybear]

Thanks PHV for your interest. That is the tack I was taking but my problem is that I need the P.Price that was in effect on the date of the transaction. Your suggestion yields the most recently entered P.Price.

For instance, if transaction t occurred on 1/15/07 and on 1/16/07 the price changed I would want the price in effect on 1/15 to show in t's row of the query.

I can't quite get my mind around the syntax of the SQL. In plain English it would be something like: "Get the price equal to the price on the greatest P.Date that is less than or equal to T.Date".

This is a sample result from your suggestion:
Site ID Date PriceDate Price Trans. Qty
001 1/11/2007 1/30/2007 $2.12 19.700
001 1/12/2007 1/30/2007 $2.12 19.900
001 1/17/2007 1/30/2007 $2.12 12.000
001 1/18/2007 1/30/2007 $2.12 17.900
001 1/21/2007 1/30/2007 $2.12 162.600
001 1/22/2007 1/30/2007 $2.12 18.900
001 1/24/2007 1/30/2007 $2.12 23.900
001 2/2/2007 1/30/2007 $2.12 11.400

Here is a copy of some Price records:
PriceDate SiteID Price
1/15/2007 002 $2.08
1/15/2007 003 $2.08
1/17/2007 002 $2.06
1/17/2007 003 $2.06
1/18/2007 002 $2.07
1/18/2007 003 $2.07
1/19/2007 001 $2.05 *
1/19/2007 002 $2.05
1/19/2007 003 $2.05
1/20/2007 002 $2.09
1/20/2007 003 $2.09
1/22/2007 002 $2.18
1/22/2007 003 $2.16
1/23/2007 002 $2.18
1/30/2007 001 $2.12 *
1/31/2007 002 $2.18
1/31/2007 003 $2.16

As you can see, there was a different price in effect for site 001 from 1/19 until 1/30. Those are the prices I need to match up to the transactions.

Again, thanks for your help, Bear

 

[PHV]

"Get the price equal to the price on the greatest P.Date that is less than or equal to T.Date".
I thought my suggested WHERE clause did exactly that ?

 

[huggybear]

PHV,

Yes, that's what I thought too, but as my samples show for some reason it won't match the right price to the date of the transaction. Take a look at the transaction for 1/21/2007. The price should be $2.05, the price that went into effect 1/19.

Thanks, Bear

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[huggybear]

PHV,

You are right, I made an error in transposing the SQL and it works just the way I intended.

Thank you so much, Bear

 

[PHV]

it works just the way [!]we[/!] intended ;-)

 

[huggybear]

That's right, how gauche of me. Please forgive me!