1

I have two tables:

  1. things:

    +------+----------------+------------+
    | id   | current_status | created_at |
    +------+----------------+------------+
    | 7770 | active         | 2016-08-09 |
    +------+----------------+------------+
    
  2. thing_status_tract:

    +----------+-------------+-----------+---------------------+
    | thing_id | status_from | status_to | changed_at          |
    +----------+-------------+-----------+---------------------+
    |     7770 | incomplete  | inactive  | 2016-08-09 16:26:22 |
    |     7770 | inactive    | active    | 2016-08-10 12:31:04 |
    +----------+-------------+-----------+---------------------+
    

I need data in the following form. This table has a status and its corresponding start and end timestamp for a particular thing_id:

+----------+-------------+---------------------+---------------------+
| thing_id | status      | status_start_date   | status_end_date     |
+----------+-------------+---------------------+---------------------+
|     7770 | incomplete  | 2016-08-09 00:00:00 | 2016-08-09 16:26:22 |
|     7770 | inactive    | 2016-08-09 16:26:22 | 2016-08-10 12:31:04 |
|     7770 | active      | 2016-08-10 12:31:04 | now()               |
+----------+-------------+---------------------+---------------------+

How to do that with a SQL query?

  • 1
    A couple of things with your design ... how do you handle the very first entry, when there is no "previous" status? What is the very first status that can possibly show up when a "thing" is first entered into the system? Second, I don't think your 'thing_status_tract' needs 'status_from' and 'status _to'. It just needs 'new_status' and date of that new status. As for actual technique, I'll give you a hint: Look at the LEAD and LAG functions. – EdStevens Sep 17 '16 at 18:20
  • Another thought - is it really necessary to have both the start and end dates on the same row in the report? Why not just the "new" status and its effective date? Anyone reading the report would know that the "end" date is the "new" date on the next row. But if the person wanting the report insists on the format you show, then definitely the LAG function. Even then, you don't need both the "old" and "new" status in the table itself. – EdStevens Sep 17 '16 at 18:35
4

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
  • Can you help me in writing the same thing in mysql (specially the partition one)? I can't seem to find an effective way. – DecoderReloaded Sep 21 '16 at 12:55
  • 1
    I don't have a mysql system and have never worked with mysql. But when I googled "oracle lead function in mysql" I got some promising hits. Again, be sure you fix your data model. Tables should be rigorously designed to Third Normal Form - designed to the nature of the data and its relationships - not to some imagined need of a particular query, report, or form. – EdStevens Sep 21 '16 at 22:08
0
SQL> create table thing_status_tract (thing_id number,
  2        new_status  varchar2(10),
  3        status_date date
  4       )
  5  ;

Table created.

SQL> insert into thing_status_tract
  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_tract
  2  values (7770,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

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

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  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_tract
  2  values (8880,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

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

1 row created.

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_tract
  7  order by thing_id,
  8        status_date;

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

6 rows selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.