Filling not complete SCD2 History

This is a solution if the scd-2 history of a dimension is not complete so there history is holey. This SQL adds all missing data.

SELECT   -98,
a.party_id_2,
1 as relation_type_id,
a.end_dt,
b.begin_dt
FROM   (SELECT   ROWNUM rn1,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) a,
(SELECT   ROWNUM rn2,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) b
WHERE       a.party_id_2 = b.party_id_2
AND rn1 = rn2 – 1
AND a.end_dt <> b.begin_dt

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.