Joining two Tables with different Intervalls

[chriys1337]

Hi,

I am having two tables where I need to bring the information together, but I am unsure how to join or intervallmatch it?

First table has the fields PERN (Person), BEGDA(startdate), ENDDA (enddate) and a attribute (1_Attribute).

Table A:

PERNR	BEGDA	ENDDA	1_Attribute
10504	01.01.2021	30.11.2022	A
10504	01.12.2022	31.12.9999	C
195486	01.01.2021	31.12.9999	D

Second table has also information about same person, but for another Attribute and the start and enddate (can but) must NOT match.

Table B:

PERNR	BEGDA	ENDDA	2_Attribute
10504	01.01.2021	31.12.9999	X
195486	01.01.2021	31.12.2021	Y
195486	01.01.2022	31.12.9999	Z

The result should look like that:

Result:

PERNR	BEGDA	ENDDA	1_Attribute	2_Attribute
10504	01.01.2021	30.11.2022	A	X
10504	01.12.2022	31.12.9999	C	X
195486	01.01.2021	31.12.2021	D	Y
195486	01.01.2022	31.12.9999	D	Z

The two first rows from table A have joined with 1st row from table B 

and third row from table A have joined with 2/3 fro table B

Does somebody please have an idea how to accomplish this?

[vincent_ardiet_]

You may have to do twice the interval join.
First Table A with Table B joining A.BEGDA with the interval between B.BEGDA and B.ENDDA.
Then, for each resulting lines, you keep the lowest ENDDA (of A.ENDDA and B.ENDDA).
And you join this resulting table with B using ENDDA.