How to get the nearest date to a record date in SQL Server?

Question

I have the following data structure:

Table A:

ID  |  RequestNumber  |  Date
----+-----------------+-----------
 1  |      1          | 2017/09/27
 2  |      1          | 2018/06/02

Table B:

RequestNumber  |  Serial  |  Date
---------------+----------+-----------
     1         |    1     | 2017/09/27
     1         |    2     | 2017/09/27
     1         |    6     | 2018/06/03
     1         |    7     | 2018/06/03
     1         |    8     | 2018/06/03

As we can see the nearest date to the first row of Table A is 2017/09/27 in the Table B and the nearest date to the second row is 2018/06/03 in the Table B

So...

I need a query to have each row from Table A with all rows from Table B that is the nearest to the record from Table A (it means 2 records should be returned the first record and 3 records should be returned for the second record)

The expected result would be:

ID  |  RequestNumber  |  Serial  |  Date
----+-----------------+----------+------------
 1  |      1          |    1     | 2017/09/27
 1  |      1          |    2     | 2017/09/27
 2  |      1          |    6     | 2018/06/03
 2  |      1          |    7     | 2018/06/03
 2  |      1          |    8     | 2018/06/03

Thanks in advance

Have you tried anything? – Prashant Pimpale Dec 23 '18 at 07:16 — Prashant Pimpale, Dec 23 '18 at 07:16
@PrashantPimpale I have no idea about it. – Hamid Reza Dec 23 '18 at 07:16 — Hamid Reza, Dec 23 '18 at 07:16

score 1 · Answer 1 · answered Dec 23 '18 at 07:52

This query will do what you want. It joins TableA to TableB on RequestNumber and then to a table of minimum DATEDIFF values between TableB and TableA, ensuring we only get the closest date in the result:

SELECT a.ID, a.RequestNumber, b.Serial, b.Date 
FROM TableA a
JOIN TableB b ON b.RequestNumber = a.RequestNumber
JOIN (SELECT a.ID AS ID, MIN(ABS(DATEDIFF(day, b.Date, a.Date))) AS days
      FROM TableA a
      JOIN TableB b ON b.RequestNumber = a.RequestNumber
      GROUP BY a.ID) c  ON c.ID = a.ID AND c.days = ABS(DATEDIFF(day, b.Date, a.Date))

Output:

ID  RequestNumber   Serial  Date
1   1               1       27/09/2017 09:30:00
1   1               2       27/09/2017 09:30:00
2   1               6       03/06/2018 09:30:00
2   1               7       03/06/2018 09:30:00
2   1               8       03/06/2018 09:30:00

Demo on dbfiddle

score 0 · Answer 2 · answered Dec 23 '18 at 07:52

Another possible approach is using LEFT JOIN and DENSE_RANK(). I assume, that you want the nearest and greater than date:

CREATE TABLE #TableA (
   ID int,
   RequestNumber int,
   [Date] date
)
CREATE TABLE #TableB (
   RequestNumber int,
   Serial int,
   [Date] date
)
INSERT INTO #TableA (ID, RequestNumber, [Date])
VALUES
   (1, 1, '2017-09-27'),
   (2, 1, '2018-06-02')

INSERT INTO #TableB (RequestNumber, Serial, [Date])
VALUES
   (1, 1, '2017-09-27'),
   (1, 2, '2017-09-27'),
   (1, 6, '2018-06-03'),
   (1, 7, '2018-06-03'),
   (1, 8, '2018-06-03'),
   (1, 9, '2018-06-05'),
   (1, 10, '2018-06-07')

; WITH cte AS (
   SELECT 
      a.ID, 
      a.RequestNumber, 
      b.Serial, 
      b.[Date], 
      DENSE_RANK() OVER (PARTITION BY a.ID, a.RequestNumber ORDER BY a.ID, a.RequestNumber, b.[Date]) AS rn
   FROM #TableA a
   LEFT JOIN #TableB b ON (a.RequestNumber = b.RequestNumber) AND (a.[Date] <= b.[Date])
)
SELECT
      ID, 
      RequestNumber, 
      Serial, 
      [Date]
FROM cte
WHERE rn = 1
ORDER BY ID, RequestNumber

Output:

ID  RequestNumber   Serial  Date
1   1   1   27/09/2017 00:00:00
1   1   2   27/09/2017 00:00:00
2   1   6   03/06/2018 00:00:00
2   1   7   03/06/2018 00:00:00
2   1   8   03/06/2018 00:00:00

score 0 · Answer 3 · answered Dec 23 '18 at 08:13

0

This will give you the desired though I dont think you have actually specified the lesser date term correctly for dates as seen from the desired output.

    Select * from table B
       left join table A
         on 
       B.requestNumber=A.requestNumber
         and B.date >=A.Date;

answered Dec 23 '18 at 08:13

Himanshu

3,830
2
10
29

if you want id on the left side you can improve my above query to `select a.id, b.* .....` – Himanshu Dec 23 '18 at 08:20

score 0 · Answer 4 · answered Dec 23 '18 at 13:13

0

This is a great place to use lateral joins (the apply keyword):

select a.*, b.*
from tablea a cross apply
     (select top (1) with ties b.*
      from tableb b
      order by abs(datediff(day, a.date, b.date))
     ) b;

Here is a db<>fiddle.

answered Dec 23 '18 at 13:13

Gordon Linoff

1,242,037
58
646
786

How to get the nearest date to a record date in SQL Server?

4 Answers4