CODEWITHSUNDEEP
sqlsql-server
Michael
Michael

Reputation: 43

SQL If data not found in similar date, get last date

I have a Table [Price] like this

Date Item Price

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

Table [Transaction]

Date Item Amount

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

I need to join these tables, with the price and transaction side by side. The rule is if the date is not found in [Price], use 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 don't know how to do this, please help if you know the solution. Thanks

Upvotes: 4

Views: 114

Answers (2)

satnhak
satnhak

Reputation: 9861

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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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;

Upvotes: 2

Related Questions