SQL Server 2022

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'),
('4', '07-Apr-2024', 'A123'),
('5', '08-Apr-2024', 'A123');
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'),
('A123', '09-Apr-2024'),
  ('A123', '11-Apr-2024');
13 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 ID  ASC   ) ClosestDt
FROM Table1 t1
 ), CTE2 AS (
  SELECT
       ID, JoiningDt, DocNum, ClosestDt
    , ROW_NUMBER() OVER(PARTITION BY DocNum,   ClosestDt ORDER BY JoiningDt) rn
  FROM CTE1
  )
SELECT * FROM CTE2


ID
JoiningDt
DocNum
ClosestDt
rn


1
2024-04-05 00:00:00.000
A123
2024-04-07 00:00:00.000
1

2
2024-04-06 00:00:00.000
A123
2024-04-07 00:00:00.000
2

4
2024-04-07 00:00:00.000
A123
2024-04-08 00:00:00.000
1

5
2024-04-08 00:00:00.000
A123
2024-04-09 00:00:00.000
1

3
2024-04-04 00:00:00.000
B123
2024-04-05 00:00:00.000
1


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 ID  ASC   ) ClosestDt
FROM Table1 t1
 ), CTE2 AS (
  SELECT
       ID, JoiningDt, DocNum, ClosestDt
    , ROW_NUMBER() OVER(PARTITION BY DocNum,   ClosestDt ORDER BY JoiningDt) rn
  FROM CTE1
  )
SELECT --* FROM CTE2
  ID, JoiningDt, DocNum, rn,
  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 order by ID


ID
JoiningDt
DocNum
rn
(No column name)


1
2024-04-05 00:00:00.000
A123
1
2024-04-07 00:00:00.000

2
2024-04-06 00:00:00.000
A123
2
2024-04-08 00:00:00.000

3
2024-04-04 00:00:00.000
B123
1
2024-04-05 00:00:00.000

4
2024-04-07 00:00:00.000
A123
1
2024-04-08 00:00:00.000

5
2024-04-08 00:00:00.000
A123
1
2024-04-09 00:00:00.000


DECLARE @ID int
DECLARE @JoiningDt DATETIME
DECLARE @DocNum VARCHAR(4)

DECLARE @MyTableVar TABLE (
    ID INT NOT NULL,
    JoiningDt datetime,
    DocNum VARCHAR(4),
    NearestDate DATETIME);


DECLARE db_cursor CURSOR FOR 
SELECT ID, JoiningDt, DocNum FROM Table1

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @ID, @JoiningDt, @DocNum

WHILE @@FETCH_STATUS = 0  
BEGIN  
INSERT INTO @MyTableVar
SELECT TOP 1 @ID, @JoiningDt, @DocNum,ClosestDt   FROM table2
WHERE DocNum = @DocNum AND ClosestDt > @JoiningDt     
  AND NOT EXISTS (SELECT 1 FROM  @MyTableVar WHERE NearestDate  = ClosestDt)
  ORDER BY ClosestDt ASC; 

      FETCH NEXT FROM db_cursor INTO @ID, @JoiningDt, @DocNum
END 

CLOSE db_cursor  
DEALLOCATE db_cursor
SELECT * FROM @MyTableVar ORDER BY ID 


ID
JoiningDt
DocNum
NearestDate


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

4
2024-04-07 00:00:00.000
A123
2024-04-09 00:00:00.000

5
2024-04-08 00:00:00.000
A123
2024-04-11 00:00:00.000

ID	JoiningDt	DocNum	ClosestDt	rn
1	2024-04-05 00:00:00.000	A123	2024-04-07 00:00:00.000	1
2	2024-04-06 00:00:00.000	A123	2024-04-07 00:00:00.000	2
4	2024-04-07 00:00:00.000	A123	2024-04-08 00:00:00.000	1
5	2024-04-08 00:00:00.000	A123	2024-04-09 00:00:00.000	1
3	2024-04-04 00:00:00.000	B123	2024-04-05 00:00:00.000	1