SQL: (Outer-)Join von VALID FROM VALID TO / AB BIS Tabellen / Zeitraumangaben / multi-temporalen Angaben

Beim JOIN von Tabellen mit AB- und BIS-Gültigkeitsangaben ist wichtig zu beachten, dass im Ergebnis des JOIN ggfs. zusätzliche Zeilen "entstehen", da sich die zusammengeführten Zeiträume möglicherweise "aufsplitten".

Ein Beispiel:
Die ID 1 und 2 haben als Kennzeichen A vom 01.01.2021 bis 14.01.2021 ein "D". Ab 15.01. wechsel das Kennzeichen auf "E".
Das Kennzeichen B der ID 1 wechselt am 15.02.2021 von "K" auf "L". ID 2 hat KEIN Kennzeichen B.

-- Left-Outer Join - korrekt
select F1.ID
     , F1.KNZ_A
     , F2.KNZ_B
     , GREATEST (F1.AB, nvl(F2.AB, F1.AB)) AB
     , LEAST (F1.BIS, nvl(F2.BIS, F1.BIS)) BIS
     , F1.AB F1_AB
     , F1.BIS F1_BIS
     , F2.AB F2_AB
     , F2.BIS F2_BIS
  from FS_AB_BIS1 F1
  left join FS_AB_BIS2 F2
        on F1.ID = F2.ID
       and F1.AB <= F2.BIS
       and F1.BIS >= F2.AB
 order by F1.ID
     , GREATEST (F1.AB, nvl(F2.AB, F1.AB))
     , LEAST (F1.BIS, nvl(F2.BIS, F1.BIS))
;

Liefert als Ergebnis:

        ID KNZ_A KNZ_B AB       BIS      F1_AB    F1_BIS   F2_AB    F2_BIS 
---------- ----- ----- -------- -------- -------- -------- -------- --------
         1 D     K     01.01.21 14.01.21 01.01.21 14.01.21 01.01.21 14.02.21
         1 E     K     15.01.21 14.02.21 15.01.21 31.12.99 01.01.21 14.02.21
         1 F     L     15.02.21 31.12.99 15.01.21 31.12.99 15.02.21 31.12.99
         2 D           01.01.21 14.01.21 01.01.21 14.01.21                 
         2 E           15.01.21 31.12.99 15.01.21 31.12.99                 

5 rows selected.

A) Der JOIN muss neben der/den ID-Spalte(n) auch die AB- und BIS-Spalten beinhalten.

B) Der resultierende AB-BIS-Zeitraum ergibt sich aus

GREATEST (F1.AB, F2.AB) und LEAST (F1.BIS, F2.BIS)

C)  Beim Outer-Join ist zu beachten, dass GREATEST und LEAST bei Null-Werten immer NULL liefern, was hier nicht gewünscht ist. Das nvl stellt sicher, dass der korrekte Wert geliefert wird.

Scripte für die Beispieltabelle:

/*** aufräumen ***/
drop table FS_AB_BIS1;
drop table FS_AB_BIS2;

/*** erstellen ***/
create table FS_AB_BIS1(
  ID  number
, KNZ_A varchar(1 char)
, AB  date
, BIS date
);

insert into FS_AB_BIS1 (ID, KNZ_A, AB, BIS)
values (1, 'D', to_date('20210101', 'yyyymmdd'), to_date('20210114', 'yyyymmdd'));
insert into FS_AB_BIS1 (ID, KNZ_A, AB, BIS)
values (1, 'E', to_date('20210115', 'yyyymmdd'), to_date('29991231', 'yyyymmdd'));
insert into FS_AB_BIS1 (ID, KNZ_A, AB, BIS)
values (2, 'D', to_date('20210101', 'yyyymmdd'), to_date('20210114', 'yyyymmdd'));
insert into FS_AB_BIS1 (ID, KNZ_A, AB, BIS)
values (2, 'E', to_date('20210115', 'yyyymmdd'), to_date('29991231', 'yyyymmdd'));
commit;

 
create table FS_AB_BIS2(
  ID  number
, KNZ_B varchar(1 char)
, AB  date
, BIS date
);

insert into FS_AB_BIS2 (ID, KNZ_B, AB, BIS)
values (1, 'K', to_date('20210101', 'yyyymmdd'), to_date('20210214', 'yyyymmdd'));
insert into FS_AB_BIS2 (ID, KNZ_B, AB, BIS)
values (1, 'L', to_date('20210215', 'yyyymmdd'), to_date('29991231', 'yyyymmdd'));
commit;