CODEWITHSUNDEEP
sqlsql-servert-sql
mich_1706
mich_1706

Reputation: 247

Joining two tables on the nearest single date

I was hoping someone might help me on this one. I have two tables that need to be joined on the nearest date (nearest before date). I have found with some searching a way to do this using the DATEDIFF and Row_Number functions, but the output is not quite what I want. Here is what i am trying to do:

CREATE TABLE #OPS ([Date] Date, [Runtime] FLOAT, [INTERVAL] INT)
INSERT INTO #OPS Values
( '2015-02-09',29540.3,12),
('2015-02-16',29661.7, 10),
('2015-03-02',29993.7,10),
('2015-03-09',30161.7,12),
('2015-03-16',30333.4,12),
('2015-03-23',30337.9,5),
('2015-03-30',30506.9,12),
('2015-04-06',30628.1,6),
('2015-04-13',30795,4),
('2015-04-20',30961.2,6)

SELECT * FROM #OPS

CREATE TABLE #APPS ([Date] DATE, [Value] INT)
 INSERT INTO #APPS Values
 ('2015-03-05', 1000),('2015-03-27', 1040), ('2015-04-17', 1070)



 ;WITH Nearest_date AS
(
    SELECT
        t1.*,  t2.Date as date2, t2.Value,
        ROW_NUMBER() OVER
        (
            PARTITION BY t1.[Date]
            ORDER BY t2.[Date] DESC
        ) AS RowNum
    FROM #OPS t1
   LEFT JOIN #APPS t2
        ON t2.[Date] <= t1.[Date]
)
SELECT *
FROM Nearest_date
WHERE RowNum = 1
ORDER BY Date ASC

--This is what I get

Date    Runtime INTERVAL    date2   Value
2/9/2015    29540.3 12  NULL    NULL
2/16/2015   29661.7 10  NULL    NULL
3/2/2015    29993.7 10  NULL    NULL
3/9/2015    30161.7 12  3/5/2015    1000
3/16/2015   30333.4 12  3/5/2015    1000
3/23/2015   30337.9 5   3/5/2015    1000
3/30/2015   30506.9 12  3/27/2015   1040
4/6/2015    30628.1 6   3/27/2015   1040
4/13/2015   30795   4   3/27/2015   1040
4/20/2015   30961.2 6   4/17/2015   1070


-- This is what I want

Date    Runtime INTERVAL    date2   Value
2/9/2015    29540.3 12  NULL    NULL
2/16/2015   29661.7 10  NULL    NULL
3/2/2015    29993.7 10  NULL    NULL
3/9/2015    30161.7 12  3/5/2015    1000
3/16/2015   30333.4 12  NULL    NULL
3/23/2015   30337.9 5   NULL    NULL
3/30/2015   30506.9 12  3/27/2015   1040
4/6/2015    30628.1 6   NULL    NULL
4/13/2015   30795   4   NULL    NULL
4/20/2015   30961.2 6   4/17/2015   1070

You can see that I want to select the nearest date that date compared against all dates in the second table. The query I created shows the same date for multiple values - when only one of those dates is truly the closest. Any help would be, as always, massively appreciated. -- running MSSQL 2014

Upvotes: 4

Views: 112

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using OUTER APPLY and LEFT JOIN:

SQL Fiddle

SELECT
    o.*,
    Date2 = t.Date,
    t.Value
FROM #OPS o
LEFT JOIN(
    SELECT
        a.*, Date2 = x.Date
    FROM #APPS a
    OUTER APPLY(
        SELECT TOP 1 *
        FROM #OPS
        WHERE
            [Date] <= a.Date
        ORDER BY [Date] DESC
    )x
)t
    ON t.Date2 = o.Date

Upvotes: 4

Related Questions