SQL getting the data by timestamp interval

I have a table that list status of works based on date.

For example:

date from	date to	Status
2023-01-01	2023-01-02	In progress
2023-01-02	2023-01-03	In progress
2023-01-03	2023-01-04	No electricty
2023-01-04	2023-01-05	In progress

I would like to summarize the table to the following:

date from	date to	Status
2023-01-01	2023-01-03	In progress
2023-01-03	2023-01-04	No electricty
2023-01-04	2023-01-05	In progress

I have tried using max/min over the columns, but I get back is the following:

date from	date to	Status
2023-01-01	2023-01-05	In progress
2023-01-03	2023-01-04	No electricty

Which is not correct.

Anyone an idea of how can I write my query to obtain my result ?

Solution

This is a gaps and islands problem in disguise. We can try using the difference in row numbers method here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY date_from) rn1,
              ROW_NUMBER() OVER (PARTITION BY Status ORDER BY date_from) rn2
    FROM yourTable
)

SELECT
    MIN(date_from) AS date_from,
    MAX(date_to) AS date_to,
    Status
FROM cte
GROUP BY
    Status,
    (rn1 - rn2);