Trying to find the most recent date where a status field has changed in Oracle SQL

I have a table that shows a full history of location ID’s (LOCN_ID), which includes an ACTIVE_STATUS field showing A for active, or I for inactive. Each time a location’s active status changes, a new record is created with a new OP_DATE. However, any time the EXTERNALLY_VISIBLE field in the table gets changed, another record with a new OP_DATE is also created.

Here is an example of this.

For each LOCN_ID in the table, I need to be able to find the most recent OP_DATE that the ACTIVE_STATUS field changed (to either I or A). I don’t care about when the EXTERNALLY_VISIBLE field changed. For the LOCN_ID shown in the example, the result should be:

    OP_DATE       LOCN_ID   ACTIVE_STATUS
  12/9/11 7:34     558732         I

There are also some cases where a LOCN_ID’s active status will have never changed, in which case the result should be the oldest OP_DATE for that LOCN_ID.

How would I be able to write a query in Oracle SQL to show this desired output for each LOCN_ID?

Answer

You have to handle both situations, when there is a row where status changed and when it’s absent. Lag() is obvious as it is designed to find previous values. Optional is older, slower self-join. Also we need row_number(), because you have complicated conditional ordering. In row_number as first part we need descending order, then ascending in case there were no status changes. It can be done like here:

select op_date, locn_id, active_status
  from (
    select a.*, row_number() 
                over (partition by locn_id 
                      order by case when active_status <> las then sysdate-op_date end, 
                               op_date) as rn
      from (select t.*, lag(active_status) over (partition by locn_id order by op_date) las 
              from t) a) 
  where rn = 1

^{dbfiddle demo}