SQL: Korrekter Join von VALID FROM 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 hat als Kennzeichen A vom 01.01.2021 bis 14.01.2021 ein "A". Ab 15.01. wechsel das Kennzeichen auf "B".
Das Kennzeichen B dieser ID wechselt am 15.02.2021 von "K" auf "L".
In getrennten Tabellen abgelegt schaut das dann so aus:
select * from FS_AB_BIS1;
ID KNZ_A AB BIS
---------- ----- -------- --------
1 A 01.01.21 14.01.21
1 B 15.01.21 31.12.99
2 rows selected.
select * from FS_AB_BIS2;
ID KNZ_B AB BIS
---------- ----- -------- --------
1 K 01.01.21 14.02.21
1 L 15.02.21 31.12.99
2 rows selected.
Der Join beider Tabellen schaut dann so aus:
/* Join ZWEI Tabellen - der einfache Fall */
select F1.ID
, F1.KNZ_A
, F2.KNZ_B
, greatest(F1.AB, F2.AB) AB
, least(F1.BIS, F2.BIS) BIS
, F1.AB
, F1.BIS
, F2.AB
, F2.BIS
from FS_AB_BIS1 F1
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, F2.AB), least(F1.BIS, F2.BIS)
;
ID KNZ_A KNZ_B AB BIS AB_1 BIS_1 AB_2 BIS_2
---------- ----- ----- -------- -------- -------- -------- -------- --------
1 A K 01.01.21 14.01.21 01.01.21 14.01.21 01.01.21 14.02.21
1 B K 15.01.21 14.02.21 15.01.21 31.12.99 01.01.21 14.02.21
1 B L 15.02.21 31.12.99 15.01.21 31.12.99 15.02.21 31.12.99
3 rows selected.Man bekommt drei Zeilen. Alles korrekt.
Wird das Ganze um eine weitere Tabelle erweitert, wird es aufwändiger:
-- Version 1
select F1.ID
, F1.KNZ_A
, F2.KNZ_B
, F3.KNZ_C
, greatest(F1.AB, F2.AB, F3.AB) AB
, least(F1.BIS, F2.BIS, F3.BIS) BIS
, F1.AB
, F1.BIS
, F2.AB
, F2.BIS
, F3.AB
, F3.BIS
from FS_AB_BIS1 F1
join FS_AB_BIS2 F2
on F1.ID = F2.ID
and F1.AB <= F2.BIS
and F1.BIS >= F2.AB
join FS_AB_BIS3 F3
on F1.ID = F3.ID
and F1.AB <= F3.BIS
and F1.BIS >= F3.AB
order by F1.ID, greatest(F1.AB, F2.AB, F3.AB), least(F1.BIS, F2.BIS, F3.BIS)
;--> *fehlerhafter* JOIN, da hier ein Zeitraum 15.03. - 14.02.(!) entsteht!
ID KNZ_A KNZ_B KNZ_C AB BIS AB_1 BIS_1 AB_2 BIS_2 AB_3 BIS_3
---------- ----- ----- ----- -------- -------- -------- -------- -------- -------- -------- --------
1 A K S 01.01.21 14.01.21 01.01.21 14.01.21 01.01.21 14.02.21 01.01.21 14.03.21
1 B K S 15.01.21 14.02.21 15.01.21 31.12.99 01.01.21 14.02.21 01.01.21 14.03.21
1 B L S 15.02.21 14.03.21 15.01.21 31.12.99 15.02.21 31.12.99 01.01.21 14.03.21
1 B K T 15.03.21 14.02.21 15.01.21 31.12.99 01.01.21 14.02.21 15.03.21 31.12.99
1 B L T 15.03.21 31.12.99 15.01.21 31.12.99 15.02.21 31.12.99 15.03.21 31.12.99
5 rows selected.
So geht es leider nicht. Denn hier entsteht ein ungültiger Zeitraum von 15.03. bis 14.02..
Korrekt ist:
-- Version 2
select F1.ID
, F1.KNZ_A
, F2.KNZ_B
, F3.KNZ_C
, greatest(F1.AB, F2.AB, F3.AB) AB
, least(F1.BIS, F2.BIS, F3.BIS) BIS
, F1.AB
, F1.BIS
, F2.AB
, F2.BIS
, F3.AB
, F3.BIS
from FS_AB_BIS1 F1
join FS_AB_BIS2 F2
on F1.ID = F2.ID
and F1.AB <= F2.BIS
and F1.BIS >= F2.AB
join FS_AB_BIS3 F3
on F1.ID = F3.ID
and greatest(F1.AB, F2.AB) <= F3.BIS
and least(F1.BIS, F2.BIS) >= F3.AB
order by F1.ID, greatest(F1.AB, F2.AB, F3.AB), least(F1.BIS, F2.BIS, F3.BIS)
;--> korrekter JOIN. evtl. unübersichtlich
ID KNZ_A KNZ_B KNZ_C AB BIS AB_1 BIS_1 AB_2 BIS_2 AB_3 BIS_3
---------- ----- ----- ----- -------- -------- -------- -------- -------- -------- -------- --------
1 A K S 01.01.21 14.01.21 01.01.21 14.01.21 01.01.21 14.02.21 01.01.21 14.03.21
1 B K S 15.01.21 14.02.21 15.01.21 31.12.99 01.01.21 14.02.21 01.01.21 14.03.21
1 B L S 15.02.21 14.03.21 15.01.21 31.12.99 15.02.21 31.12.99 01.01.21 14.03.21
1 B L T 15.03.21 31.12.99 15.01.21 31.12.99 15.02.21 31.12.99 15.03.21 31.12.99
4 rows selected.
Ebenfalls korrekt und ggfs. etwas strukturierter ist Version 3:
-- Version 3, geschachtelt
select FA.ID
, FA.KNZ_A
, FA.KNZ_B
, F3.KNZ_C
, greatest(FA.AB, F3.AB) AB
, least(FA.BIS, F3.BIS) BIS
, FA.AB
, FA.BIS
, F3.AB
, F3.BIS
from (
-- Tbl F1 + F2
select F1.ID
, F1.KNZ_A
, F2.KNZ_B
, greatest(F1.AB, F2.AB) AB
, least(F1.BIS, F2.BIS) BIS
from FS_AB_BIS1 F1
join FS_AB_BIS2 F2
on F1.ID = F2.ID
and F1.AB <= F2.BIS
and F1.BIS >= F2.AB
) FA
join FS_AB_BIS3 F3
on FA.ID = F3.ID
and FA.AB <= F3.BIS
and FA.BIS >= F3.AB
order by FA.ID, greatest(FA.AB, F3.AB), least(FA.BIS, F3.BIS)
;--> korrekter JOIN. übersichtlicher?
ID KNZ_A KNZ_B KNZ_C AB BIS AB_1 BIS_1 AB_2 BIS_2
---------- ----- ----- ----- -------- -------- -------- -------- -------- --------
1 A K S 01.01.21 14.01.21 01.01.21 14.01.21 01.01.21 14.03.21
1 B K S 15.01.21 14.02.21 15.01.21 14.02.21 01.01.21 14.03.21
1 B L S 15.02.21 14.03.21 15.02.21 31.12.99 01.01.21 14.03.21
1 B L T 15.03.21 31.12.99 15.02.21 31.12.99 15.03.21 31.12.99
4 rows selected.
Bei entsprechend mehr Tabellen wird es dann wirklich unübersichtlich. Vor allem, wenn die IDs nicht so einfach wie im Beispiel sind.
Scripte zur Erstellung der Beispieltabellen:
/*** aufräumen ***/
drop table FS_AB_BIS1;
drop table FS_AB_BIS2;
drop table FS_AB_BIS3;
/*** 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, 'A', to_date('20210101', 'yyyymmdd'), to_date('20210114', 'yyyymmdd'));
insert into FS_AB_BIS1 (ID, KNZ_A, AB, BIS)
values (1, 'B', 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;
create table FS_AB_BIS3(
ID number
, KNZ_C varchar(1 char)
, AB date
, BIS date
);
insert into FS_AB_BIS3 (ID, KNZ_C, AB, BIS)
values (1, 'S', to_date('20210101', 'yyyymmdd'), to_date('20210314', 'yyyymmdd'));
insert into FS_AB_BIS3 (ID, KNZ_C, AB, BIS)
values (1, 'T', to_date('20210315', 'yyyymmdd'), to_date('29991231', 'yyyymmdd'));
commit;
Kommentare
Kommentar veröffentlichen