Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table test_history
- (
- id int,
- prop VARCHAR(16)
- );
- INSERT into test_history values ( 1,'a');
- INSERT into test_history values ( 2,'b');
- create TABLE updated_test ( id int ,prop VARCHAR(16));
- INSERT into updated_test values ( 1,'c');
- create table test_history_all
- (
- id int,
- prop VARCHAR(16),
- begin_date date,
- end_date date
- );
- insert into test_history_all
- select
- id,
- prop,
- '2017-01-01',
- '3000-12-31'
- from test_history;
- select
- alls.id,
- alls.prop,
- alls.begin_date,
- case
- when
- alls.end_date = '3000-12-31' and updated.id is not null then cast('2017-01-02' as date) else cast('3000-12-31' as date)
- end
- from test_history_all alls left join updated_test updated on alls.id = updated.id
- union all
- select
- alls.id,
- updated.prop,
- cast('2017-01-02' as date),
- end_date
- from test_history_all alls
- JOIN updated_test updated on alls.id = updated.id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement