Finding the closest date to a date from a set of dates

1

Hi:
Here's what I am trying to do _- I am trying to get the price closest to the Trade date in the following table from a price table.
I have a table with the following data

Trades:
ID TradeDate TradePrice
1 1/25/2016 98
1 12/31/2015 97

And
Another table with the following

Price:
ID PriceDate Price
1 6/1/2015 84
1 1/5/2016 89
1 1/13/12016 95
1 1/23/2016 96
1 1/29/2016 92

The desired result is

ID TradeDate TradePrice Pricedate Price
1 1/25/2016 98 1/23/2016 96
1 12/31/2015 97 1/5/2016 89

Any help is greatly appreciated!!

Thanks In Advance

2

When you say closest price , what this means ?

  • the most recent to trade date, but smaller
  • the most recent to trade date, but bigger
  • or else?
3 
DECLARE  @Trades TABLE 
    ([ID] int, [TradeDate] datetime, [TradePrice] int);    
INSERT INTO @Trades
    ([ID], [TradeDate], [TradePrice])
VALUES
    (1, '2016-01-25 02:00:00', 98),
    (1, '2015-12-31 02:00:00', 97);


DECLARE @Price TABLE 
    ([ID] int, [PriceDate] datetime, [Price] int);
    
INSERT INTO @Price
    ([ID], [PriceDate], [Price])
VALUES
    (1, '2015-06-01 03:00:00', 84),
    (1, '2016-01-05 02:00:00', 89),
    (1, '2016-01-13 02:00:00', 95),
    (1, '2016-01-23 02:00:00', 96),
    (1, '2016-01-29 02:00:00', 92);

The query:

SELECT
    T.ID
    ,CONVERT(DATE,T.TradeDate,104) AS TradeDate
    ,T.TradePrice
    ,CONVERT(DATE,CA.PriceDate,104) AS PriceDate
    ,CA.Price
FROM
    @Trades AS T
    CROSS APPLY
    (SELECT TOP (1)
        PriceDate
        ,Price
    FROM
        @Price AS P
    WHERE
        T.ID = P.ID
        --AND T.TradeDate > PriceDate
    ORDER BY
        ABS(DATEDIFF(DAY,T.TradeDate ,P.PriceDate)) ASC) CA

The output:

ID          TradeDate  TradePrice  PriceDate  Price
1           2016-01-25 98          2016-01-23 96
1           2015-12-31 97          2016-01-05 89
1 Like
4

Thanks Stepson. Worked great!!