Search code examples
sqlsql-serverdateclosest

Closest Date in SQL

I am trying to find nearest date or actual date between Table_A and Table_B in SQL Server 2012

Table_A

Date
-------
2017-07-15 00:00:00
2017-07-27 00:00:00
2017-07-23 00:00:00

Table_B

DT
------
2017-07-17 00:00:00
2017-07-19 00:00:00
2017-07-23 00:00:00
2017-07-28 00:00:00

Conditions:

  • If Table_A.Date = Table_B.DT then Table_A.Date
  • If table_A.Date <> Table_B.DT then whichever next higher date in Table_B

Desired output:

Date
-----
2017-07-17 00:00:00
2017-07-23 00:00:00
2017-07-28 00:00:00

Any help or guidance?

Solution

  • Use CROSS APPLY and TOP:

    SELECT 
    Date = x.DT
FROM Table_A a
CROSS APPLY(
    SELECT TOP(1) DT
    FROM Table_B b
    WHERE b.DT >= a.Date
    ORDER BY b.DT
) x
ORDER BY x.DT