— Verdichten/Densification von Zeitranges mit Berücksichtigung der Reihenfolge

Condensing/Densification or Merging of Date Ranges in one table with considering the chronology.
This is necessary if the Table is a subset of columns of the original table
There can be changes in attributes which are not in the table to condense causing the generation of new versions.
We now need to condense or merge the date ranges so we get as a result for every change in our table only one row.

/*
Verdichtet eine Tabelle mit BK und einer History,
falls UNMITTELBAR AUFEINANDER FOLGEND gleiche Attributsauspraegungen vorhanden sind.
Diese fallen dann zusammen.
—-
This logic condenses a table concerning consecutively history-rows with the same attributes.
These Rows are combined.
History-holes are considered!
*/

–drop table CONDENSING_TEST purge;

CREATE TABLE CONDENSING_TEST
(
H_KONTO_S_CID NUMBER, — = Primary key
H_KONTO_CID NUMBER, — = Businesskey
C_START_DT DATE,
C_END_DT DATE,
TESTKONTO_FLG NUMBER,
SONDERKONTO_FLG NUMBER,
EXISTIERT_FLG NUMBER,
BEZUG_FLG NUMBER,
BARZAHLUNG_FLG NUMBER
)
TABLESPACE CORE_DTA;


SET DEFINE OFF;
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('28.06.1984 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('06.02.2003 11:49:47','DD.MM.RRRR HH24:MI:SS'), '1','-1','1','1','1');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('06.02.2003 11:49:48','DD.MM.RRRR HH24:MI:SS'),to_date('13.08.2004 14:23:09','DD.MM.RRRR HH24:MI:SS'), '2','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('03.03.1994 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.2009 23:59:59','DD.MM.RRRR HH24:MI:SS'), '3','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('03.03.1994 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.2009 23:59:59','DD.MM.RRRR HH24:MI:SS'), '4','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('13.08.2004 14:23:10','DD.MM.RRRR HH24:MI:SS'),to_date('13.08.2004 14:23:24','DD.MM.RRRR HH24:MI:SS'), '5','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('13.08.2004 14:23:25','DD.MM.RRRR HH24:MI:SS'),to_date('14.03.2005 09:57:42','DD.MM.RRRR HH24:MI:SS'), '6','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('14.03.2005 09:57:43','DD.MM.RRRR HH24:MI:SS'),to_date('07.10.2005 09:59:31','DD.MM.RRRR HH24:MI:SS'), '7','-1','1','1','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43481',to_date('07.10.2005 09:59:32','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'), '8','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('01.01.2010 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'), '9','-1','1','0','0');
Insert into condensing_test (H_KONTO_S_CID,H_KONTO_CID,C_START_DT,C_END_DT,TESTKONTO_FLG,SONDERKONTO_FLG,EXISTIERT_FLG,BEZUG_FLG,BARZAHLUNG_FLG) values ('0','43482',to_date('01.01.2010 00:00:00','DD.MM.RRRR HH24:MI:SS'),to_date('31.12.9999 23:59:59','DD.MM.RRRR HH24:MI:SS'),'10','-1','1','0','0');
commit;

/*
Beruecksichtigte beschreibende Attribute:
Considered descriptive Attributes:
-TESTKONTO_FLG
-SONDERKONTO_FLG
-EXISTIERT_FLG
-BEZUG_FLG
-BARZAHLUNG_FLG
*/
select H_KONTO_CID
,C_START_DT
,C_END_DT
,TESTKONTO_FLG
,SONDERKONTO_FLG
,EXISTIERT_FLG
,BEZUG_FLG
,BARZAHLUNG_FLG
from condensing_test
order by h_konto_cid,C_START_DT;

— H_KONTO_CID/HUB_CID entspricht dem Businesskey aus der Business-View
— H_KONTO_CID/HUB_CID correlates to the Businesskey, coming from the Business-View-Logic

with res01 as
(select H_KONTO_CID as hub_cid
,C_START_DT as C_START_DT
,C_END_DT as C_END_DT
,testkonto_flg as attrib1
,lag ( testkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib1_LAG
,lead( testkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib1_LEAD
,sonderkonto_flg as attrib2
,lag ( sonderkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib2_LAG
,lead( sonderkonto_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib2_LEAD
,existiert_flg as attrib3
,lag ( existiert_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib3_LAG
,lead( existiert_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib3_LEAD
,bezug_flg as attrib4
,lag ( bezug_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib4_LAG
,lead( bezug_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib4_LEAD
,barzahlung_flg as attrib5
,lag ( barzahlung_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib5_LAG
,lead( barzahlung_flg, 1 ) over (partition by H_KONTO_CID order by C_START_DT) as attrib5_LEAD
from condensing_test
where h_konto_cid in (43481,43482)
)
,
res02 as
(-- nimm die Zeile, wenn aktuelle Auspraegung nicht der Vorgaengerversion entspricht
-- take the row if current occurrence not equal to the previous occurrence
select HUB_CID
,C_START_DT as CHANGE
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,1 as TAKEROW
from res01
where (attrib1_LAG != attrib1 or attrib1_LAG is null)
or (attrib2_LAG != attrib2 or attrib2_LAG is null)
or (attrib3_LAG != attrib3 or attrib3_LAG is null)
or (attrib4_LAG != attrib4 or attrib4_LAG is null)
or (attrib5_LAG != attrib5 or attrib5_LAG is null)
union all
-- nimm die Zeile NICHT, wenn sich danach was geaendert hat, d.h.
-- wenn aktuelle Auspraegung nicht der Nachfolgeversion entspricht
-- don't take the row, if current occurrence is not equals to the previous occurrence
select HUB_CID
,C_END_DT as CHANGE
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,0 as TAKEROW
from res01
where (attrib1_LEAD != attrib1 or attrib1_LEAD is null)
or (attrib2_LEAD != attrib2 or attrib2_LEAD is null)
or (attrib3_LEAD != attrib3 or attrib3_LEAD is null)
or (attrib4_LEAD != attrib4 or attrib4_LEAD is null)
or (attrib5_LEAD != attrib5 or attrib5_LEAD is null)
)
,
res03 as
(
select HUB_CID
,CHANGE as C_START_DT
,lead( CHANGE, 1 ) over (partition by HUB_CID order by CHANGE) as C_END_DT --HUB_CID anstatt TABLE_CID
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,TAKEROW
from res02)
select HUB_CID as H_KONTO_CID
,C_START_DT as C_START_DT
,C_END_DT as C_END_DT
,0 AS c_audit_cid
,attrib1 as testkonto_flg
,attrib2 as sonderkonto_flg
,attrib3 as existiert_flg
,attrib4 as bezug_flg
,attrib5 as barzahlung_flg
from res03
where TAKEROW = 1
order by HUB_CID, C_START_DT;