Combination of SCD2 Histories

This is a solution for combinate two scd2-histories into one new scd2-history

drop table hist1 purge;
drop table hist2 purge;
create table hist1
(
hist1_id varchar2(10)
, join_key number
, begin_dt date
, end_dt   date
);
create table hist2
(
hist2_id varchar2(10)
, join_key number
, begin_dt date
, end_dt   date
);

insert into hist1(hist1_id, join_key, begin_dt, end_dt)
values(‚A‘,11,to_date(‚20100101′,’yyyymmdd‘),to_date(‚20120601′,’yyyymmdd‘));
insert into hist1(hist1_id, join_key, begin_dt, end_dt)
values(‚B‘,11,to_date(‚20110601′,’yyyymmdd‘),to_date(‚20121201′,’yyyymmdd‘));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‚1‘,11,to_date(‚20110101′,’yyyymmdd‘),to_date(‚20120201′,’yyyymmdd‘));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‚2‘,11,to_date(‚20110201′,’yyyymmdd‘),to_date(‚20120701′,’yyyymmdd‘));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‚3‘,11,to_date(‚20110701′,’yyyymmdd‘),to_date(‚20121101′,’yyyymmdd‘));
commit;

create or replace view hist1_hist2 as
WITH — all distinct dates in both histories
days AS (SELECT   begin_dt dt FROM hist1
UNION
SELECT   end_dt FROM hist1
UNION
SELECT   begin_dt FROM hist2
UNION
SELECT   end_dt FROM hist2),
history1 AS (SELECT   d.dt,
h1.hist1_id,
h1.join_key,
h1.begin_dt,
h1.end_dt
FROM   days d, hist1 h1
WHERE   d.dt >= h1.begin_dt(+) AND d.dt < h1.end_dt(+)),
history2 AS (SELECT   d.dt,
h2.hist2_id,
h2.join_key,
h2.begin_dt,
h2.end_dt
FROM   days d, hist2 h2
WHERE   d.dt >= h2.begin_dt(+) AND d.dt < h2.end_dt(+))
SELECT   hist1_id,
hist2_id,
join_key,
CASE
WHEN h1_begin_dt < h2_begin_dt THEN h2_begin_dt
ELSE h1_begin_dt
END
AS begin_dt,
CASE WHEN h1_end_dt > h2_end_dt THEN h2_end_dt ELSE h1_end_dt END
AS end_dt
—     , h1_begin_dt
—     , h1_end_dt
—     , h2_begin_dt
—     , h2_end_dt
FROM   (  SELECT   –DISTINCT                                  —     , h_1.dt
h_1.hist1_id,
h_2.hist2_id,
h_1.join_key,
h_1.begin_dt AS h1_begin_dt,
h_1.end_dt AS h1_end_dt,
h_2.begin_dt AS h2_begin_dt,
h_2.end_dt AS h2_end_dt
FROM   history1 h_1, history2 h_2
WHERE   1 = 1 AND h_1.dt = h_2.dt AND h_1.join_key = h_2.join_key
)
;

Schreibe einen Kommentar