Sql-server – How to get the nearest date to a record date in sql

querysql serversql-server-2012

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

Best Answer

Inner sub-query returns the nearest date.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM
    ta
JOIN
    tb
    ON tb.RequestNumber = ta.RequestNumber
    AND tb.dt = (SELECT TOP 1 
                     dt
                 FROM
                     tb
                 WHERE
                     RequestNumber = ta.RequestNumber
                     AND dt >= ta.dt 
                 ORDER BY
                     RequestNumber,
                     dt ASC);

Or you cans use a DENSE_RANK to flag rows by RequestNumber + Date and get the rank = 1.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM   
    ta
CROSS APPLY (
             SELECT RequestNumber, Serial, dt,
                    DENSE_RANK() OVER (PARTITION BY RequestNumber 
                                 ORDER BY RequestNumber, dt) rn
             FROM tb
             WHERE tb.RequestNumber = ta.RequestNumber
                   AND tb.dt >= ta.dt
            ) tb
WHERE rn = 1;

Both queries return the same result:

ID | RequestNumber | Serial | dt                 
-: | ------------: | -----: | :------------------
 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

db<>fiddle here

Related Question