By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (
ID INTEGER,
JoiningDt DATETIME,
DocNum VARCHAR(4)
);
INSERT INTO Table1
(ID, JoiningDt, DocNum)
VALUES
('1', '05-Apr-2024', 'A123'),
('2', '06-Apr-2024', 'A123'),
('3', '04-Apr-2024', 'B123');
CREATE TABLE Table2 (
DocNum VARCHAR(4),
ClosestDt DATETIME
);
INSERT INTO Table2
(DocNum, ClosestDt)
VALUES
('A123', '03-Apr-2024'),
('A123', '04-Apr-2024'),
('A123', '07-Apr-2024'),
('A123', '08-Apr-2024'),
('B123', '02-Apr-2024'),
('B123', '05-Apr-2024');
9 rows affected
WITH CTE1 As (
SELECT t1.ID, t1.JoiningDt, t1.DocNum,
(SELECT TOP 1 ClosestDt FROM Table2
WHERE DocNum = t1.DocNum AND ClosestDt > t1.JoiningDt ORDER BY ClosestDt ASC ) ClosestDt
FROM Table1 t1
), CTE2 AS (
SELECT
ID, JoiningDt, DocNum, ClosestDt
, ROW_NUMBER() OVER(PARTITION BY DocNum, ClosestDt ORDER BY ID) rn
FROM CTE1
)
SELECT ID, JoiningDt, DocNum,
CASE WHEN rn = 1 then ClosestDt ELSE
(SELECT ClosestDt FROM Table2
WHERE DocNum = c1.DocNum AND ClosestDt > c1.JoiningDt ORDER BY ClosestDt ASC
OFFSET c1.rn -1 ROWS FETCH NEXT 1 ROWS ONLY) END
FROM CTE2 c1
| ID | JoiningDt | DocNum | (No column name) |
|---|---|---|---|
| 1 | 2024-04-05 00:00:00.000 | A123 | 2024-04-07 00:00:00.000 |
| 2 | 2024-04-06 00:00:00.000 | A123 | 2024-04-08 00:00:00.000 |
| 3 | 2024-04-04 00:00:00.000 | B123 | 2024-04-05 00:00:00.000 |