create table tmm_d_entitle_admin(
admin_pk int primary key,
soeid varchar2(15),
start_date date,
end_date date
)
;
insert into tmm_d_entitle_admin values(1,‘xy55456‘,to_date(‘2014-09-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));
insert into tmm_d_entitle_admin values(2,‘sw55123‘,to_date(‘2014-10-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

create table tmm_m_gdw(gdwpk int primary key,
soeid varchar2(15),goc_code varchar2(50),
start_date date, end_date date
);
insert into tmm_m_gdw values(1,‘xy55456‘,‘goc1‘,to_date(‘2014-09-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));
insert into tmm_m_gdw values(2,‘sw55123‘,‘goc2‘,to_date(‘2014-10-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

select * from tmm_m_gdw ;

create table goc_mca(feed_id varchar2(50),mca_pk int,start_date date, end_date date);
insert into goc_mca values(‘goc2‘,2,to_date(‘2014-10-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));
insert into goc_mca values(‘goc1‘,1,to_date(‘2014-10-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

insert into goc_mca values(‘goc1‘,3,to_date(‘2014-10-09‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

select * from goc_mca;

create table mca(mca_pk int ,mca_id varchar2(50), start_date date, end_date date);
insert into mca values(1,1,to_date(‘2014-10-10‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));
insert into mca values(2,2,to_date(‘2014-10-15‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

insert into mca values(3,3,to_date(‘2014-10-15‘,‘yyyy-mm-dd‘),to_date(‘9999-12-30‘,‘yyyy-mm-dd‘));

create table tmm_f_entitle_admin(f_pk int primary key,admin_pk int, soeid varchar2(15), goc_code varchar2(50), mca_id varchar2(50),mca_pk int,start_date date, end_date date);
select * from tmm_f_entitle_admin;

create sequence seq_f_entitle_admin_pk start with 1;
---new data from dimension table self changes or add new
merge into (select * from tmm_f_entitle_admin  where end_date=to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)) t
using (
select distinct admin.admin_pk,admin.soeid,gdw.goc_code,mca.mca_id,mca.mca_pk,
greatest(admin.start_date,gdw.start_date,goc_mca.start_date) as new_start_date, admin.start_date from tmm_d_entitle_admin admin
join tmm_m_gdw gdw on admin.soeid=gdw.soeid
join goc_mca on gdw.goc_code=goc_mca.feed_id
join mca on mca.mca_pk=goc_mca.mca_pk
) s
on (t.admin_pk=s.admin_pk)
when not matched then insert
values(seq_f_entitle_admin_pk.nextval,
s.admin_pk,s.soeid,
s.goc_code,s.mca_id,s.mca_pk,s.start_date,
to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)
);
select * from tmm_f_entitle_admin;

--insert rows for goc_code changes
merge into (select * from tmm_f_entitle_admin  where end_date=to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)) t
using (
select distinct admin.admin_pk,admin.soeid,gdw.goc_code,mca.mca_id,mca.mca_pk,
greatest(admin.start_date,gdw.start_date,goc_mca.start_date) as new_start_date, admin.start_date from tmm_d_entitle_admin admin
join tmm_m_gdw gdw on admin.soeid=gdw.soeid
join goc_mca on gdw.goc_code=goc_mca.feed_id
join mca on mca.mca_pk=goc_mca.mca_pk
) s
on (t.admin_pk=s.admin_pk and t.mca_id=s.mca_id and t.goc_code=s.goc_code and t.mca_id=s.mca_id )
when matched then
update end_date=s.start_date
where coalesce(t.mca_pk,-1)<>coalesce(s.mca_pk,-1)
when not matched then insert
values(seq_f_entitle_admin_pk.nextval,
s.admin_pk,s.soeid,
s.goc_code,s.mca_id,s.mca_pk,s.new_start_date,
to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)
);

---insert mca_pk change rows
merge into (select * from tmm_f_entitle_admin  where end_date=to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)) t
using (
select distinct admin.admin_pk,admin.soeid,gdw.goc_code,mca.mca_id,mca.mca_pk,
greatest(admin.start_date,gdw.start_date,goc_mca.start_date) as new_start_date, admin.start_date from tmm_d_entitle_admin admin
join tmm_m_gdw gdw on admin.soeid=gdw.soeid
join goc_mca on gdw.goc_code=goc_mca.feed_id
join mca on mca.mca_pk=goc_mca.mca_pk
) s
on (t.admin_pk=s.admin_pk and t.mca_id=s.mca_id and t.goc_code=s.goc_code and t.mca_id=s.mca_id and t.mca_pk=s.mca_pk)
when not matched then insert
values(seq_f_entitle_admin_pk.nextval,
s.admin_pk,s.soeid,
s.goc_code,s.mca_id,s.mca_pk,s.new_start_date,
to_date(‘9999-12-30‘,‘yyyy-mm-dd‘)
);

--- 1 xy55456  goc2 mca_id 3
--- 1 xy55456  goc1 mca_id 3
--如果mcaid and adminpk 不唯一的话 则需要把goc code也加上去作为唯一键
--mca id 没变 mca_pk 发生变化的时候 then should use update insert
--update not exists

work diary