Skip to content
Advertisement

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:

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?

Advertisement

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:

dbfiddle demo

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement