SQL If data is not found on the same date, get the latest date

Question

I have a [Price] table like this

Date Product Price

 1-1-2012 Potion 500 1-1-2012 Elixir 5000 1-10-2012 Potion 600 

Table [Transaction]

Date Element Amount

1-1-2012 Potion 1
1-5-2012 Potion 1

I need to join these tables, with price and transaction side by side. Rule: if the date is not found in [Price], use the latest price

So, 1-5-2012 Potion will cost 500

The result should be like this:

Date Item Amount Price

1-1-2012 Potion 1,500
1-5-2012 Potion 1,500

So, I do not know how to do this, please help if you know the solution. Thanks

Answer 1

A simple SCALAR SUBQUERY will do.

 select t.date, t.item, t.amount, (select top(1) price from price where t.item=p.item and p.date <= t.date order by p.date desc) price from [transaction] t; 

Answer 2

This should work:

  SELECT t.Date, t.Item, t.Amount, ISNULL( p.Price, ( SELECT TOP 1 p1.Price FROM [Price] p1 WHERE p1.Item = t.Item ORDER BY Date DESC ) [Price] FROM [Transaction] t LEFT OUTER JOIN [Price] p ON t.Date = p.Date AND t.Item = p.Item