How can I join two tables on an ID and a DATE RANGE in SQL-CodePudding

I have 2 query result tables containing records for different assessments. There are RAssessments and NAssessments which make up a complete review.

The aim is to eventually determine which reviews were completed. I would like to join the two tables on the ID, and on the date, HOWEVER the date each assessment is completed on may not be identical and may be several days apart, and some ID's may have more of an RAssessment than an NAssessment.

Therefore, I would like to join T1 on to T2 on ID & on T1Date( or - 7 days). There is no other way to match the two tables and to align the records other than using the date range, as this is a poorly designed database. I hope for some help with this as I am stumped.

Here is some sample data:

Table #1:

ID	RAssessmentDate
1	2020-01-03
1	2020-03-03
1	2020-05-03
2	2020-01-09
2	2020-04-09
3	2022-07-21
4	2020-06-30
4	2020-12-30
4	2021-06-30
4	2021-12-30

Table #2:

ID	NAssessmentDate
1	2020-01-07
1	2020-03-02
1	2020-05-03
2	2020-01-09
2	2020-07-06
2	2020-04-10
3	2022-07-21
4	2021-01-03
4	2021-06-28
4	2022-01-02
4	2022-06-26

I would like my end result table to look like this:

ID	RAssessmentDate	NAssessmentDate
1	2020-01-03	2020-01-07
1	2020-03-03	2020-03-02
1	2020-05-03	2020-05-03
2	2020-01-09	2020-01-09
2	2020-04-09	2020-04-10
2	NULL	2020-07-06
3	2022-07-21	2022-07-21
4	2020-06-30	NULL
4	2020-12-30	2021-01-03
4	2021-06-30	2021-06-28
4	2021-12-30	2022-01-02
4	NULL	2022-01-02

CodePudding user response：

Try this:

SELECT 
    COALESCE(a.ID, b.ID) ID, 
    a.RAssessmentDate, 
    b.NAssessmentDate
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RowId, * 
    FROM table1
) a
FULL OUTER JOIN (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RowId, * 
    FROM table2
) b ON a.ID = b.ID AND a.RowId = b.RowId
WHERE (a.RAssessmentDate BETWEEN '2020-01-01' AND '2022-01-02') 
    OR (b.NAssessmentDate BETWEEN '2020-01-01' AND '2022-01-02')