Oracle query to find the least change before a new change in a given data set

Question

I am trying to do an update on a table using its own data, and i am struggling to come up with a query for my requirement

please see the data below

KEY  VALID_DATE  STATUS    INACTIVE_DT 

1    7/2/2019    ACTIVE   
1    7/27/2018   INACTIVE
1    8/19/2018   INACTIVE
1    9/7/2018    ACTIVE
1    1/15/2019   INACTIVE
1    1/21/2019   INACTIVE 

2   3/31/2019    INACTIVE
2   4/1/2019     INACTIVE

3   7/2/2019     ACTIVE 
3   7/3/2019     INACTIVE
3   7/4/2019     INACTIVE
3   7/5/2019     ACTIVE
3   7/6/2019     INACTIVE

I need to update inactive_dt field on the same table using the conditions below.

1) DATA needs to be partitioned/grouped by the KEY column.

2) populate inactive_dt whenever the status becomes inactive using the valid_date field

3) for the consecutive inactive dates retain the first inactive date.

4) if there is a active dt in between two inactive dates then the second inactive date should get the latest value rather than the first value

see the out put below

KEY  VALID_DATE  STATUS        INACTIVE_DT 

    1    7/2/2019    ACTIVE    NULL
    1    7/27/2018   INACTIVE  7/27/2018
    1    8/19/2018   INACTIVE  7/27/2018
    1    9/7/2018    ACTIVE    NULL
    1    1/15/2019   INACTIVE  1/15/2019
    1    1/21/2019   INACTIVE  1/15/2019

    2   3/31/2019    INACTIVE  3/31/2019
    2   4/1/2019     INACTIVE  3/31/2019
    2   6/1/2019     ACTIVE    NULL

    3   7/2/2019     ACTIVE    NULL
    3   7/3/2019     INACTIVE  7/3/2019
    3   7/4/2019     INACTIVE  7/3/2019
    3   7/5/2019     ACTIVE    NULL
    3   7/6/2019     INACTIVE  7/6/2019

Could you please give me suggestions for the best way to achieve this.

Thanks for the help

Your point#3 conflict with the expected output. – Popeye Aug 16 '19 at 15:45 — Popeye, Aug 16 '19 at 15:45
Sorry, i edited the output now. Thank you. – user1751356 Aug 16 '19 at 15:49 — user1751356, Aug 16 '19 at 15:49

score 3 · Accepted Answer · answered Aug 16 '19 at 16:15

You could sum rows when status changes, this gives you possibility to use this column as additional partitioning key:

select key, valid_date, status, grp,
       case 
         when status = 'ACTIVE' then null
         else first_value(valid_date) over (partition by key, grp, status)
       end dt
  from (select t.*, sum(case status when 'ACTIVE' then 1 end) 
                        over (partition by key order by valid_date) grp from t)
  order by key, valid_date

^{dbfiddle demo}

Oracle query to find the least change before a new change in a given data set

1 Answers1