Get length of time while status remains the same

I have a history table which looks like this:

| ID | DateChanged | Status |
|----|-------------|--------|
| 1  | 01/01/2019  | Closed |
| 1  | 05/01/2019  | Open   |
| 1  | 10/01/2019  | Open   |
| 2  | 01/02/2019  | Open   |
| 2  | 05/02/2019  | Closed |
| 3  | 01/03/2019  | Open   |
| 3  | 01/05/2019  | Open   |
| 3  | 01/06/2019  | Open   |

The history table is only ever updated when a change occurs, so I can say that the row returned by MAX(DateChanged) is the current state.

Let’s say that ID relates to a shop.

I want to be able to identify the length of time that a currently open shop has been open for.

So I would get an output like this:

| ID | DateOpen   |
|----|------------|
| 1  | 05/01/2019 |
| 2  | (null)     |
| 3  | 01/03/2019 |

ID = 2 would return null because it is currently closed.

I have tried using LAG to get the previous record:

lag(status) over (partition by id order by datechanged)

But I can’t work out how to recursively increase the step until it finds “closed” rather than “open” and return that date.

How could I achieve this?

Answer

If there are only two statuses, you can use conditional aggregation to get the duration.

select id,
       (max(datechanged) -
        coalesce(max(case when status = 'closed' then datechanged end),
                 min(datechanged) -- in case there are no closed records
                )
       )
from t
group by id;

To get the earliest open date, you can use window functions:

select id,
       min(case when status = 'Open' and
                     (datechanged > closed_datechanged or
                      closed_datechanged is null)
                then datechanged 
           end) as open_datetime
from (select t.*,
             max(case when status = 'Closed' then datechanged end) over (PARTITION BY id) as closed_datechanged 
        FROM t)
group by id;

The subquery gets the latest closed date time. This is then used to get the first open date after that.