Find the Closest Date Match for Each Record from Two Tables — From SQL to SPL #20

Problem description & analysis:

SQL Server has two tables, Table1：

Table2：

Task: 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.

Code comparisons:

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: SPL can directly implement it according to business logic.👉🏻 Try.DEMO

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.

Free to Try, Powerful to Use — esProc SPL FREE Download.