merge-history-kimberghansen

View All Scripts Login to Run Script

Script Name merge-history-kimberghansen
Description Kim Berg Hansen's solution to my SQL problem "Merge Record History Tables" http://jeffkemponoracle.com/2015/10/14/merge-two-record-history-tables-in-sql/
Area SQL General
Contributor Jeffrey Kemp
Created Thursday October 15, 2015

Statement 1

create table t1 
  (deptid number not null 
  ,from_date date not null 
  ,to_date date not null 
  ,data1 varchar2(30) not null 
  ,primary key (deptid, from_date) 
  )

Table created.

Statement 2

create table t2 
  (deptid number not null 
  ,from_date date not null 
  ,to_date date not null 
  ,data2 varchar2(30) not null 
  ,primary key (deptid, from_date) 
  )

Table created.

Statement 3

insert into t1 values (4001, date'2015-01-01', date'2015-01-31', 'jan data 1')

1 row(s) inserted.

Statement 4

insert into t1 values (4001, date'2015-02-01', date'2015-02-28', 'feb data 1')

1 row(s) inserted.

Statement 5

insert into t2 values (4001, date'2015-01-15', date'2015-02-14', 'jan-feb data 2')

1 row(s) inserted.

Statement 6

insert into t2 values (4001, date'2015-02-15', date'2015-03-14', 'feb-mar data 2')

1 row(s) inserted.

Statement 7

insert into t2 values (4001, date'2015-03-15', date'2015-04-14', 'mar-apr data 2')

1 row(s) inserted.

Statement 8

insert into t1 values (4002, date'2015-01-15', date'2015-02-14', 'jan-feb data 1')

1 row(s) inserted.

Statement 9

insert into t1 values (4002, date'2015-02-15', date'2015-03-14', 'feb-mar data 1')

1 row(s) inserted.

Statement 10

insert into t1 values (4002, date'2015-03-15', date'2015-04-14', 'mar-apr data 1')

1 row(s) inserted.

Statement 11

insert into t2 values (4002, date'2015-01-01', date'2015-01-31', 'jan data 2')

1 row(s) inserted.

Statement 12

insert into t2 values (4002, date'2015-02-01', date'2015-02-28', 'feb data 2')

1 row(s) inserted.

Statement 13

insert into t1 values (4003, date'2015-01-01', date'2015-01-31', 'jan data 1')

1 row(s) inserted.

Statement 14

insert into t2 values (4003, date'2015-01-01', date'2015-01-31', 'jan data 2')

1 row(s) inserted.

Statement 15

insert into t1 values (4004, date'2015-01-01', date'2015-01-31', 'jan data 1')

1 row(s) inserted.

Statement 16

insert into t2 values (4004, date'2015-01-10', date'2015-01-20', 'jan data int 2')

1 row(s) inserted.

Statement 17

insert into t1 values (4005, date'2015-01-10', date'2015-01-20', 'jan data int 1')

1 row(s) inserted.

Statement 18

insert into t2 values (4005, date'2015-01-01', date'2015-01-31', 'jan data 2')

1 row(s) inserted.

Statement 19

insert into t1 values (4006, date'2015-01-01', date'2015-01-01', 'jan data a 1')

1 row(s) inserted.

Statement 20

insert into t1 values (4006, date'2015-01-02', date'2015-01-31', 'jan data b 1')

1 row(s) inserted.

Statement 21

insert into t2 values (4006, date'2015-01-01', date'2015-01-31', 'jan data 2')

1 row(s) inserted.

Statement 22

insert into t1 values (4007, date'2015-01-01', date'2015-01-31', 'jan data 1')

1 row(s) inserted.

Statement 23

insert into t2 values (4007, date'2015-01-01', date'2015-01-01', 'jan data a 2')

1 row(s) inserted.

Statement 24

insert into t2 values (4007, date'2015-01-02', date'2015-01-31', 'jan data b 2')

1 row(s) inserted.

Statement 25

insert into t1 values (4008, date'2015-01-01', date'2015-01-31', 'jan data 1')

1 row(s) inserted.

Statement 26

insert into t2 values (4008, date'2015-01-09', date'2015-01-17', 'jan data a 2')

1 row(s) inserted.

Statement 27

insert into t2 values (4008, date'2015-01-18', date'2015-01-26', 'jan data b 2')

1 row(s) inserted.

Statement 28

insert into t2 values (4008, date'2015-01-27', date'2015-02-04', 'jan-feb data 2')

1 row(s) inserted.

Statement 29

insert into t1 values (4009, date'2015-01-09', date'2015-01-17', 'jan data a 1')

1 row(s) inserted.

Statement 30

insert into t1 values (4009, date'2015-01-18', date'2015-01-26', 'jan data b 1')

1 row(s) inserted.

Statement 31

insert into t1 values (4009, date'2015-01-27', date'2015-02-04', 'jan-feb data 1')

1 row(s) inserted.

Statement 32

insert into t2 values (4009, date'2015-01-01', date'2015-01-31', 'jan data 2')

1 row(s) inserted.

Statement 33

with v1 as ( 
   select t.deptid 
        , case r.l 
             when 1 then t.from_date 
             when 2 then date '0001-01-01' 
             when 3 then t.max_to + 1 
          end from_date 
        , case r.l 
             when 1 then t.to_date 
             when 2 then t.min_from -1 
             when 3 then date '9999-01-01' 
          end to_date 
        , case r.l 
             when 1 then t.data1 
          end data1 
     from ( 
      select deptid 
           , from_date 
           , to_date 
           , data1 
           , row_number() over (partition by deptid order by from_date) rn 
           , min(from_date) over (partition by deptid) min_from 
           , max(to_date) over (partition by deptid) max_to 
        from t1 
     ) t, lateral(  
         select level l 
           from dual 
         connect by level <= case t.rn when 1 then 3 else 1 end 
     ) r 
), v2 as ( 
   select t.deptid 
        , case r.l 
             when 1 then t.from_date 
             when 2 then date '0001-01-01' 
             when 3 then t.max_to + 1 
          end from_date 
        , case r.l 
             when 1 then t.to_date 
             when 2 then t.min_from -1 
             when 3 then date '9999-01-01' 
          end to_date 
        , case r.l 
             when 1 then t.data2 
          end data2 
     from ( 
      select deptid 
           , from_date 
           , to_date 
           , data2 
           , row_number() over (partition by deptid order by from_date) rn 
           , min(from_date) over (partition by deptid) min_from 
           , max(to_date) over (partition by deptid) max_to 
        from t2 
     ) t, lateral(  
         select level l 
           from dual 
         connect by level <= case t.rn when 1 then 3 else 1 end 
     ) r 
) 
select v1.deptid 
     , greatest(v1.from_date, v2.from_date) from_date 
     , least(v1.to_date, v2.to_date) to_date 
     , v1.data1 
     , v2.data2 
  from v1 
  join v2 
      on v2.deptid = v1.deptid 
     and v2.to_date >= v1.from_date 
     and v2.from_date <= v1.to_date 
 where v2.data2 is not null or v1.data1 is not null 
 order by v1.deptid 
        , greatest(v1.from_date, v2.from_date)

 
DEPTID FROM_DATE TO_DATE DATA1 DATA2
4001 01-JAN-15 14-JAN-15 jan data 1  - 
4001 15-JAN-15 31-JAN-15 jan data 1 jan-feb data 2
4001 01-FEB-15 14-FEB-15 feb data 1 jan-feb data 2
4001 15-FEB-15 28-FEB-15 feb data 1 feb-mar data 2
4001 01-MAR-15 14-MAR-15  - feb-mar data 2
4001 15-MAR-15 14-APR-15  - mar-apr data 2
4002 01-JAN-15 14-JAN-15  - jan data 2
4002 15-JAN-15 31-JAN-15 jan-feb data 1 jan data 2
4002 01-FEB-15 14-FEB-15 jan-feb data 1 feb data 2
4002 15-FEB-15 28-FEB-15 feb-mar data 1 feb data 2
4002 01-MAR-15 14-MAR-15 feb-mar data 1  - 
4002 15-MAR-15 14-APR-15 mar-apr data 1  - 
4003 01-JAN-15 31-JAN-15 jan data 1 jan data 2
4004 01-JAN-15 09-JAN-15 jan data 1  - 
4004 10-JAN-15 20-JAN-15 jan data 1 jan data int 2
4004 21-JAN-15 31-JAN-15 jan data 1  - 
4005 01-JAN-15 09-JAN-15  - jan data 2
4005 10-JAN-15 20-JAN-15 jan data int 1 jan data 2
4005 21-JAN-15 31-JAN-15  - jan data 2
4006 01-JAN-15 01-JAN-15 jan data a 1 jan data 2
4006 02-JAN-15 31-JAN-15 jan data b 1 jan data 2
4007 01-JAN-15 01-JAN-15 jan data 1 jan data a 2
4007 02-JAN-15 31-JAN-15 jan data 1 jan data b 2
4008 01-JAN-15 08-JAN-15 jan data 1  - 
4008 09-JAN-15 17-JAN-15 jan data 1 jan data a 2
4008 18-JAN-15 26-JAN-15 jan data 1 jan data b 2
4008 27-JAN-15 31-JAN-15 jan data 1 jan-feb data 2
4008 01-FEB-15 04-FEB-15  - jan-feb data 2
4009 01-JAN-15 08-JAN-15  - jan data 2
4009 09-JAN-15 17-JAN-15 jan data a 1 jan data 2
4009 18-JAN-15 26-JAN-15 jan data b 1 jan data 2
4009 27-JAN-15 31-JAN-15 jan-feb data 1 jan data 2
4009 01-FEB-15 04-FEB-15 jan-feb data 1  - 

33 rows selected.

DEPTID	FROM_DATE	TO_DATE	DATA1	DATA2
4001	01-JAN-15	14-JAN-15	jan data 1	-
4001	15-JAN-15	31-JAN-15	jan data 1	jan-feb data 2
4001	01-FEB-15	14-FEB-15	feb data 1	jan-feb data 2
4001	15-FEB-15	28-FEB-15	feb data 1	feb-mar data 2
4001	01-MAR-15	14-MAR-15	-	feb-mar data 2
4001	15-MAR-15	14-APR-15	-	mar-apr data 2
4002	01-JAN-15	14-JAN-15	-	jan data 2
4002	15-JAN-15	31-JAN-15	jan-feb data 1	jan data 2
4002	01-FEB-15	14-FEB-15	jan-feb data 1	feb data 2
4002	15-FEB-15	28-FEB-15	feb-mar data 1	feb data 2
4002	01-MAR-15	14-MAR-15	feb-mar data 1	-
4002	15-MAR-15	14-APR-15	mar-apr data 1	-
4003	01-JAN-15	31-JAN-15	jan data 1	jan data 2
4004	01-JAN-15	09-JAN-15	jan data 1	-
4004	10-JAN-15	20-JAN-15	jan data 1	jan data int 2
4004	21-JAN-15	31-JAN-15	jan data 1	-
4005	01-JAN-15	09-JAN-15	-	jan data 2
4005	10-JAN-15	20-JAN-15	jan data int 1	jan data 2
4005	21-JAN-15	31-JAN-15	-	jan data 2
4006	01-JAN-15	01-JAN-15	jan data a 1	jan data 2
4006	02-JAN-15	31-JAN-15	jan data b 1	jan data 2
4007	01-JAN-15	01-JAN-15	jan data 1	jan data a 2
4007	02-JAN-15	31-JAN-15	jan data 1	jan data b 2
4008	01-JAN-15	08-JAN-15	jan data 1	-
4008	09-JAN-15	17-JAN-15	jan data 1	jan data a 2
4008	18-JAN-15	26-JAN-15	jan data 1	jan data b 2
4008	27-JAN-15	31-JAN-15	jan data 1	jan-feb data 2
4008	01-FEB-15	04-FEB-15	-	jan-feb data 2
4009	01-JAN-15	08-JAN-15	-	jan data 2
4009	09-JAN-15	17-JAN-15	jan data a 1	jan data 2
4009	18-JAN-15	26-JAN-15	jan data b 1	jan data 2
4009	27-JAN-15	31-JAN-15	jan-feb data 1	jan data 2
4009	01-FEB-15	04-FEB-15	jan-feb data 1	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database