From SQL to SPL: Find the closest date match for each record from two tables

SQL Server has two tables, Table1

ID

JoiningDt

DocNum

1

2024-04-05

A123

2

2024-04-06

A123

3

2024-04-04

B123

Table2

DocNum ClosestDt
A123 2024-04-03
A123 2024-04-04
A123 2024-04-07
A123 2024-04-08
B123 2024-04-02
B123 2024-04-05


Now we need to sort Table1 by ID and traverse each record in sequence: retrieve the record in Table2 that has the same DocNum field as the current record, but with a slightly later time (the earliest among all later records). The special rule is that records taken from Table2 cannot be retrieved again next time.

ID

JoiningDt

DocNum

ClosestDt

1

2024-04-05

A123

2024-04-07

2

2024-04-06

A123

2024-04-08

3

2024-04-04

B123

2024-04-05

SQL

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 as ClosestDt
  FROM CTE2 c1

Ordered calculations need to be performed here, especially to implement the rule that record cannot be retrieved again after being taken. SQL needs to create sequence numbers and flag bits, and multiple layers of nesting are used in conjunction with join statements to indirectly implement it. The code is cumbersome and difficult to understand; Using stored procedures would be relatively intuitive, but the code would be longer and the structure would become more complex. SPL can directly implement it according to business logic. https://try.esproc.com/splx?2HC


 A

1

$select * from Table1.txt order by ID

2

$select * from Table2.txt order by ClosestDt

3

=A1.derive(A2.delete@n([A2.select@1(DocNum==A1.DocNum && ClosestDt>A1.JoiningDt)]).ClosestDt)

A1-A2 Load data.

A3 Loop through each record in A1 and add new fields. The business logic is to retrieve the record from A2 with the same DocNum as the current record but later, retrieve the ClosestDt of the first record, and then delete this record from A2 to avoid being retrieved again. Function select@1 means to select the first record that meets the criteria.

Question sourcehttps://stackoverflow.com/questions/78206862/find-the-distinct-closest-date-match-for-each-record-from-2-tables