2

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

1
  • How you are defining nearest? is it if the date falls between the last 2 or next 2 days or is there any logic for that?
    – Biju jose
    Dec 23, 2018 at 8:00

2 Answers 2

2

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

1

Try this,

declare @tableA table(ID int,RequestNumber int,  Dates date)
insert into @tableA VALUES
(1,1,'2017/09/27')
,(2 ,1,'2018/06/02')

 --select * from @tableA

declare @tableB table(RequestNumber int,Serial int, Dates date)
insert into @tableB 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')

;

WITH CTE
AS (
    SELECT A.*
        ,b.Dates AS DateA
    FROM @tableA A
    OUTER APPLY (
        SELECT TOP 1 Dates
        FROM @tableB B
        WHERE B.Dates >= a.Dates
        ORDER BY b.Dates
        ) B
    )
SELECT c.id
    ,b.RequestNumber
    ,b.Serial
    ,b.Dates
FROM @tableB B
LEFT JOIN CTE C ON b.Dates = c.DateA

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.