SQL with dates - How do I do this ? 1

[Exie]

Hi,

I have 2 tables with dates like so:
[tt]TableA
-------
ProductID Date Value
TRUCK 16/10/08 $5
TRUCK 10/10/08 $4.5
TRUCK 03/10/08 $4
CAR 15/10/08 $4.25[/tt]

[tt]TableB
-------
SaleId Customer ProductID SaleDate
1010 Customer1 TRUCK 11/10/08
1011 Customer2 TRUCK 11/10/08
1012 Customer3 TRUCK 05/10/08
1013 Customer1 CAR 15/10/08[/tt]

Now .. I want to select all the sales from TableB and work out what the price was at the time of sale.

eg. Sale 1010 should have been for $4.50
Sales 1012 should have been for $4 as it took place before the price rise.

SELECT b.SaleId,b.Customer,a.Value
FROM TableA a,
TableB b
WHERE a.ProductID = b.ProductID
and a.SaleDate = ?

 

[fosa]

Perhaps

SELECT b.SaleId,b.Customer,a.Value
FROM TableA a,
TableB b
WHERE a.ProductID = b.ProductID
and a.SaleDate =to_date('11/10/08','DD/MM/YY');


Bye

 

[kskid]

Try this

SELECT b.SaleId,b.Customer,a.Value
FROM TableA a,
TableB b
WHERE a.ProductID = b.ProductID
and a.SaleDate = (select max(a1.saledate)
from tablea a1
where a1.saledate <= b.saledate and
and a1.productid = b.productid)