MySQL and Oracle – How to Get Status Start_Date and End_Date from Status_Track Table

Of course, my previous answer was just to answer your immediate question. As I mentioned in my comments, your data model itself is flawed. Not only should you not keep both 'status_from' (prevous_status) and 'status_to' in the history table, you should also NOT keep status at all in the THINGS table. This violates standard data normalization rules.

What you should do is drop status from the THINGS table, establish a FK relationship between the two tables, and use a proper query to derive the current status when needed.

First, create and populate the THINGS table. I've added a couple of columns just to indicate the kinds of things that should be in this table:

S

QL> create table things (thing_id number,
  2                       attrib_a varchar2(10),
  3                       attrib_b varchar2(10),
  4                         constraint thing_pk primary key (thing_id)
  5                      )
  6  ;

Table created.

SQL> insert into things
  2     values (7770,
  3             'red',
  4             'white'
  5            )
  6  ;

1 row created.

SQL> insert into things
  2     values (8880,
  3             'blue',
  4             'green'
  5            )
  6  ;

1 row created.

Then create and populate the history tracking table:

SQL> create table thing_status
  2       (thing_id number,
  3        new_status  varchar2(10),
  4        status_date date,
  5          constraint fk_things
  6            foreign key (thing_id)
  7            references things(thing_id)
  8        )
  9  ;

Table created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (7770,
  3             'incomplete',
  4             to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'inactive',
  4             to_date('2016-08-10 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'active',
  4             to_date('2016-08-11 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (8880,
  3             'incomplete',
  4             to_date('2016-08-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'inactive',
  4             to_date('2016-08-13 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'active',
  4             to_date('2016-08-14 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'expired',
  4             to_date('2016-08-15 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

And your history report (as before)

SQL> -- -----------------------------------------------------
SQL> --  Select status history
SQL> --
SQL> select thing_id,
  2         new_status,
  3         to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4         lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5           over (partition by thing_id order by status_date) status_end_date
  6  from thing_status
  7  order by thing_id,
  8           status_date
  9  ;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-10 16:26:22
      7770 inactive   10-Aug-2016 16:26:22 2016-08-11 12:32:04
      7770 active     11-Aug-2016 12:32:04
      8880 incomplete 12-Aug-2016 00:00:00 2016-08-13 16:26:22
      8880 inactive   13-Aug-2016 16:26:22 2016-08-14 12:32:04
      8880 active     14-Aug-2016 12:32:04 2016-08-15 12:32:04
      8880 expired    15-Aug-2016 12:32:04

7 rows selected.

And your current status report

SQL> -- -----------------------------------------------------
SQL> --  Select current status
SQL> --
SQL> select t.thing_id,
  2         t.attrib_a,
  3         t.attrib_b,
  4         s.new_status curr_status,
  5         to_char(s.status_date,'yyyy-mm-dd hh24:mi:ss') status_date
  6  from things t
  7   join thing_status s
  8     on t.thing_id = s.thing_id
  9  where s.status_date = (select max(status_date)
 10                         from thing_status x
 11                         where s.thing_id = x.thing_id
 12                         group by thing_id
 13                        )
 14  order by t.thing_id
 15  ;

  THING_ID ATTRIB_A   ATTRIB_B   CURR_STATU STATUS_DATE
---------- ---------- ---------- ---------- -------------------
      7770 red        white      active     2016-08-11 12:32:04
      8880 blue       green      expired    2016-08-15 12:32:04