Joining two tables with unequal dates 1

[hkaing79]

I have two tables:

Table1: Product, SaleDate
Table2: Product, SaleDate, Price

I'd like to join the two tables on Product and the closest SaleDate. For example, Table1 has a SaleDate of 8/18/05, while Table2 has SaleDate's of 7/1/05, 8/1/05, and 9/1/05 for the same product. I'd like to match this product to Table2.SaleDate = 8/1/05.

Any ideas?

 

[PHV]

SELECT A.Product, A.SaleDate, B.Price
FROM Table1 AS A INNER JOIN Table2 AS B ON A.Product=B.Product
WHERE B.SaleDate = (SELECT Max(SaleDate) FROM Table2 WHERE Product=A.Product AND SaleDate<=A.SaleDate);


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

 

[hkaing79]

You DA MAN!