CODEWITHSUNDEEP
sql-servert-sql
gordon613
gordon613

Reputation: 2952

VLOOKUP style lookup in SQL Server 2012

I have a table of data which stores prices for different commodities on different dates.

CREATE TABLE COMMODITIES(
    [MARKET_DATE] [datetime2](0) NOT NULL,
    [THE_TYPE] [numeric](2, 0) NOT NULL,
    [CLOSE_VALUE] [numeric](10, 4) NULL,
) ON [PRIMARY]

GO

MARKET_DATE holds the trading date (the dates are in ascending order, but non-sequential)

CLOSE_VALUE holds the price

THE_TYPE holds the type of commodity.

The trading dates for each commodity are different.

I need to get a list of all dates where THE_TYPE=1, with the price, as well as the corresponding price for the commodity with THE_TYPE=2

If the trading dates for each commodity were the same I could do a simple join (equivalent to the Excel VLOOKUP (, false)

SELECT mr1.market_date
    ,mr1.close_value,mr2.close_value

FROM commodities mr1
LEFT JOIN commodities  mr2 ON mr1.market_date = mr2.market_date
    AND mr2.the_type = 2
WHERE mr1.the_type = 1

However because the trading dates are not the same, I need to look for the a non-exact match equivalent to the Excel VLOOKUP (, true) - i.e. the maximum date in mr2 which is on or before mr1.market_date

I am looking for a neat solution. I could as a workaround enter the intervening dates into the database which would work. Or alternatively I could do the following

SELECT mr1.market_date
    ,mr1.close_value
    , 

            (SELECT top 1 close_value
            FROM commodities mr2

            where mr2.market_date <= mr1.market_date
                AND mr2.the_type = 2
                order by market_date desc
            ) AS close_value

FROM commodities mr1
WHERE mr1.the_type = 1

but I hope there is a neater (and quicker) solution

As an example, if the table holds the following

MARKET_DATE CLOSE_VALUE  THE_TYPE
    2012-01-01  300  1
    2012-01-03  310  1
    2012-01-05  310  1
    2012-01-01  400  2
    2012-01-02  420  2
    2012-01-05  430  2

I would get

2012-01-01  300  400
2012-01-03  310  420
2012-01-05  310  430

Upvotes: 3

Views: 3660

Answers (2)

paparazzo
paparazzo

Reputation: 45106

WITH OrderedOrders AS
(
    SELECT mr1.market_date
          ,mr1.close_value as mr1close
          ,mr2.close_value as mr2close
          ,ROW_NUMBER() OVER (PARTITION BY mr1.market_date ORDER BY mr2.market_date desc) AS RowNumber
    FROM commodities mr1
    JOIN commodities mr2
      ON mr2.market_date <= mr1.market_date
     AND mr2.the_type = 2
     AND mr1.the_type = 1 
)  
SELECT market_date, mr1close, mr2close  
FROM OrderedOrders 
WHERE RowNumber = 1

Upvotes: 1

rivarolle
rivarolle

Reputation: 1538

You could use CROSS APPLY. It might be slightly faster but probably not noticeable for this case:

SELECT mr1.market_date
    ,mr1.close_value as close_value_1
    ,p.close_value as close_value_2
FROM commodities mr1
cross apply
  (SELECT top 1 close_value
        FROM commodities mr2
        where mr2.market_date <= mr1.market_date
            AND mr2.the_type = 2
            order by market_date desc) p
  WHERE mr1.the_type = 1

See it in action

Upvotes: 2

Related Questions