Search code examples
sqloraclewindow-functionslag

Using LAG function with a specific condition

I have a table in below format.

enter image description here

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

enter image description here

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.

enter image description here

How can we do this in Oracle SQL?

Solution

  • 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