Using LAG function with a specific condition

Question

I have a table in below format.

I am able to convert the table to below format using LAG function in oracle SQL.

However, I want this result table to be created in such a way that for rows where status is 'ON', to_date should provide the date of next 'OFF' date. Below is how it should look like.

How can we do this in Oracle SQL?

Answer 1

I think that you want a conditional lead() with option ignore nulls:

select 
    id,
    date from_date,
    case when status = 'ON' 
        then lead(case when status = 'OFF' then date end ignore nulls) 
            over(partition by id order by date)
    end to_date,
    status
from mytable

To better match the expected results, we apply the function to any status other than 'OFF', and withdraw 1 day from the result:

select 
    id,
    date from_date,
    case when status <> 'OFF' 
        then lead(case when status = 'OFF' then date end ignore nulls) 
            over(partition by id order by date) - 1
    end to_date,
    status
from mytable

If you also want the next 'ON' date for 'OFF' rows:

select 
    id,
    date from_date,
    case when status <> 'OFF' 
        then lead(case when status = 'OFF' then date end ignore nulls) 
            over(partition by id order by date) - 1
        else lead(case when status = 'ON' then date end ignore nulls) 
            over(partition by id order by date) - 1
    end to_date,
    status
from mytable

Demo on DB Fiddlde

First query:

   ID | FROM_DATE | TO_DATE   | STATUS
----: | :-------- | :-------- | :-----
15643 | 10-MAR-20 | 20-MAR-20 | ON    
15643 | 15-MAR-20 | null      | test  
15643 | 20-MAR-20 | null      | OFF   

Second query:

   ID | FROM_DATE | TO_DATE   | STATUS
----: | :-------- | :-------- | :-----
15643 | 10-MAR-20 | 19-MAR-20 | ON    
15643 | 15-MAR-20 | 19-MAR-20 | test  
15643 | 20-MAR-20 | null      | OFF   

Third query (you can't see the difference with the second query since there is no 'OFF' row with a next 'ON'):

   ID | FROM_DATE | TO_DATE   | STATUS
----: | :-------- | :-------- | :-----
15643 | 10-MAR-20 | 19-MAR-20 | ON    
15643 | 15-MAR-20 | 19-MAR-20 | test  
15643 | 20-MAR-20 | null      | OFF