Hi Tom,
I want to backtrack each single record to find its original states. The student table contains the updated values only, however student history contains what was the old value for that updated columns.
create table student
(
id number(6) primary key,
name varchar2(50),
city varchar2(50),
address varchar2(100),
createdDateTime date,
updatedDatetime date);
insert into student values(1,'abc1','abc1','abc1','09-Jan-20','12-Jan-20');
insert into student values(2,'pqr','pqr','pqr','09-Jan-20',null);
student table-
ID Name City Address Create_time UpdatedTime
1 abc1 abc1 abc1 09-Jan-20 12-Jan-20
2 pqr pqr pqr 09-Jan-20 null
create table studentHistory
(
id number(6) ,
name varchar2(50),
city varchar2(50),
address varchar2(100),
DatetimeCreated date
);
insert into StudentHistory values(1,null,'abc',null,'10-Jan-20');
insert into StudentHistory values(1,'abc',null,null,'11-Jan-20');
insert into StudentHistory values(1,null,null,'abc','12-Jan-20');
Student history table-
ID Name City Address DatetimeCreated
1 null abc null 10-Jan-20
1 abc null null 11-Jan-20
1 null null abc 12-Jan-20
required output will be each stages of single record-
ID Name City Address DatetimeCreated LastUpdated
1 abc abc abc 09-Jan-20 null
1 abc abc1 abc 09-Jan-20 10-Jan-20
1 abc1 abc1 abc 09-Jan-20 11-Jan-20
1 abc1 abc1 abc1 09-Jan-20 12-Jan-20
2 pqr pqr pqr 09-Jan-20 null
SQL> create table student
2 (
3 id number(6) primary key,
4 name varchar2(50),
5 city varchar2(50),
6 address varchar2(100),
7 createdDateTime date,
8 updatedDatetime date);
Table created.
SQL>
SQL> insert into student values(1,'abc1','abc1','abc1','09-Jan-20','12-Jan-20');
1 row created.
SQL> insert into student values(2,'pqr','pqr','pqr','09-Jan-20',null);
1 row created.
SQL>
SQL>
SQL> create table studentHistory
2 (
3 id number(6) ,
4 name varchar2(50),
5 city varchar2(50),
6 address varchar2(100),
7 DatetimeCreated date
8 );
Table created.
SQL>
SQL> insert into StudentHistory values(1,null,'abc',null,'10-Jan-20');
1 row created.
SQL> insert into StudentHistory values(1,'abc',null,null,'11-Jan-20');
1 row created.
SQL> insert into StudentHistory values(1,null,null,'abc','12-Jan-20');
1 row created.
--
-- first step is to get all the records we'll need
--
SQL>
SQL> with all_recs as
2 ( -- initial creation
3 select id, name, city, address, createdDateTime, cast(null as date) updatedDatetime
4 from student
5 union all
6 -- all the updates
7 select id, name, city, address, null createdDateTime, DatetimeCreated
8 from studentHistory
9 )
10 select *
11 from all_recs
12 order by id, updatedDatetime nulls first;
ID NAME CITY ADDRESS CREATEDDA UPDATEDDA
---------- -------------------- ---------- ---------- --------- ---------
1 abc1 abc1 abc1 09-JAN-20
1 abc 10-JAN-20
1 abc 11-JAN-20
1 abc 12-JAN-20
2 pqr pqr pqr 09-JAN-20
5 rows selected.
--
-- now I'll use ROW_NUMBER to assign an order based on create/update timestamp
--
SQL>
SQL>
SQL> with all_recs as
2 ( -- initial creation
3 select id, name, city, address, createdDateTime, cast(null as date) updatedDatetime
4 from student
5 union all
6 -- all the updates
7 select id, name, city, address, null createdDateTime, DatetimeCreated
8 from studentHistory
9 )
10 select
11 id,
12 name,
13 city,
14 address,
15 createdDateTime,
16 updatedDatetime,
17 row_number() over ( partition by id order by nvl(createdDateTime,updatedDatetime)) as seq
18 from all_recs
19 order by id, updatedDatetime nulls first;
ID NAME CITY ADDRESS CREATEDDA UPDATEDDA SEQ
---------- -------------------- ---------- ---------- --------- --------- ----------
1 abc1 abc1 abc1 09-JAN-20 1
1 abc 10-JAN-20 2
1 abc 11-JAN-20 3
1 abc 12-JAN-20 4
2 pqr pqr pqr 09-JAN-20 1
5 rows selected.
--
-- then I can use NVL/LAG to "fill in the blanks" as needed
--
SQL>
SQL>
SQL>
SQL> with all_recs as
2 ( -- initial creation
3 select id, name, city, address, createdDateTime, cast(null as date) updatedDatetime
4 from student
5 union all
6 -- all the updates
7 select id, name, city, address, null createdDateTime, DatetimeCreated
8 from studentHistory
9 ),
10 sequenced_rows as (
11 select
12 id,
13 name,
14 city,
15 address,
16 createdDateTime,
17 updatedDatetime,
18 row_number() over ( partition by id order by nvl(createdDateTime,updatedDatetime)) as seq
19 from all_recs
20 )
21 select
22 id,
23 city,
24 address,
25 nvl(createdDateTime,
26 lag(createdDateTime ignore nulls) over ( partition by id order by seq ) ) createdDateTime,
27 updatedDatetime
28 from sequenced_rows;
ID CITY ADDRESS CREATEDDA UPDATEDDA
---------- ---------- ---------- --------- ---------
1 abc1 abc1 09-JAN-20
1 abc 09-JAN-20 10-JAN-20
1 09-JAN-20 11-JAN-20
1 abc 09-JAN-20 12-JAN-20
2 pqr pqr 09-JAN-20
5 rows selected.
SQL>
SQL>