SCD type 2

scd-type2.sql

create table medal_scd (
  id number(20),
  quantity number(20),
  start_date date,
  end_date date,
  status char (1 byte)
);

create table medal_src (
  id number(20),
  quantity number(20),
  date_modified date,
  dml_type char (1 byte)
);


insert into medal_scd values (1, 0, to_date('07/02/2014', 'dd/mm/yyyy'), to_date('31/12/9999', 'dd/mm/yyyy'), 'A');
insert into medal_scd values (2, 0, to_date('07/02/2014', 'dd/mm/yyyy'), to_date('31/12/9999', 'dd/mm/yyyy'), 'A');
insert into medal_scd values (3, 0, to_date('07/02/2014', 'dd/mm/yyyy'), to_date('31/12/9999', 'dd/mm/yyyy'), 'A');
insert into medal_scd values (4, 0, to_date('07/02/2014', 'dd/mm/yyyy'), to_date('31/12/9999', 'dd/mm/yyyy'), 'A');
commit;

insert into medal_src values (1, 33, to_date('23/02/2014', 'dd/mm/yyyy'), 'U');
insert into medal_src values (2, 26, to_date('23/02/2014', 'dd/mm/yyyy'), 'U');
insert into medal_src values (3, 25, to_date('23/02/2014', 'dd/mm/yyyy'), 'U');
insert into medal_src values (4, 0, to_date('23/02/2014', 'dd/mm/yyyy'), 'D');
commit;


merge
  into medal_scd scd
  using (
          select id,
                 quantity,
                 date_modified,
                 dml_type
          from   medal_src
          union  all
          select -id,
                 quantity,
                 date_modified,
                 dml_type
          from   medal_src
          where  dml_type <> 'D'
        ) src
  on (scd.id = src.id)
  when matched
  then
    update
    set    scd.status = case
                          when src.dml_type = 'D'
                          then
                            'A'
                          else
                            'N'
                        end,
           scd.end_date = src.date_modified
    where  scd.status = 'A'
  when not matched 
  then
    insert (
             id,
             quantity,
             start_date,
             end_date,
             status
           )
    values (
             -src.id,
             src.quantity,
             src.date_modified,
             case
               when src.dml_type = 'D'
               then
                 src.date_modified
               else
                 to_date('31/12/9999', 'dd/mm/yyyy')
             end,
             'A'
           )
    where  src.id < 0
;
commit;