Joining two Tables with different Intervalls

chriys1337 — Tue, 26 Sep 2023 08:14:27 GMT

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?

Re: Joining two Tables with different Intervalls

vincent_ardiet_ — Tue, 26 Sep 2023 13:17:59 GMT

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.

topic Re: Joining two Tables with different Intervalls in App Development

Joining two Tables with different Intervalls

Re: Joining two Tables with different Intervalls