return nearest adjacent rows that are earlier/later by date to another table's date-CodePudding

Problem: to return a single result set based on the following

for each date in report table (rdate) return the latest transaction table (tdate) that is earlier or equal to rdate (plus t1 & t2) as sdate,s1,s2

for each date in report table (rdate) return the earliest transaction table (tdate) that is later or equal to rdate (plus t1 & t2) as edate,e1,e2

Have attempted TOP 1 / APPLY / LEAD& LAG code, but cannot get results as required.

Any advice is appreciated. Thanks

Report table

rdate
06/01/2021
26/01/2021
15/02/2021

Transaction table

tdate	t1	t2
01/01/2021	17	6
05/01/2021	5	9
09/01/2021	8	12
19/01/2021	15	11
20/01/2021	12	8
25/01/2021	9	1
26/01/2021	8	17
30/01/2021	7	6
08/02/2021	6	21
22/02/2021	14	5
27/02/2021	11	4

Result required

rdate	sdate	s1	s2	edate	e1	e2
06/01/2021	05/01/2021	5	9	09/01/2021	8	12
26/01/2021	26/01/2021	8	17	26/01/2021	8	17
15/02/2021	08/02/2021	6	21	22/02/2021	14	5

CodePudding user response：

A CROSS APPLY or OUTER APPLY together with a SELECT TOP 1 with appropriate order and filter criteria should do the trick. Try:

DECLARE @ReportTable TABLE (rdate DATETIME)
INSERT @ReportTable
VALUES
    ('2021-01-06'),
    ('2021-01-26'),
    ('2021-02-15')

DECLARE @TransactionTable TABLE (tdate DATETIME, t1 INT, t2 INT)
INSERT @TransactionTable
VALUES
    ('2021-01-01', 17, 6),
    ('2021-01-05', 5, 9),
    ('2021-01-09', 8, 12),
    ('2021-01-19', 15, 11),
    ('2021-01-20', 12, 8),
    ('2021-01-25', 9, 1),
    ('2021-01-26', 8, 17),
    ('2021-01-30', 7, 6),
    ('2021-02-08', 6, 21),
    ('2021-02-22', 14, 5),
    ('2021-02-27', 11, 4)

SELECT * -- TODO: Assign meaningful names here
FROM @ReportTable R
OUTER APPLY (
    SELECT TOP 1 *
    FROM @TransactionTable T1
    WHERE T1.tdate <= R.rdate
    ORDER BY T1.tdate DESC
) S
OUTER APPLY (
    SELECT TOP 1 *
    FROM @TransactionTable T2
    WHERE T2.tdate >= R.rdate
    ORDER BY T2.tdate
) E
ORDER BY R.rdate

The OUTER APPLY is like the LEFT JOIN equivalent of CROSS APPLY, allowing for no record found. Take a close look at the inequality conditions to ensure that the edge cases are as intended.