CODEWITHSUNDEEP
sqlsql-serverdateclosest
Jayank
Jayank

Reputation: 91

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:

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?

Upvotes: 0

Views: 1259

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 3

Related Questions