SQL: Merge almost identical lines with start_date and end_date

Question

I have the following rows:

id	status	startdate	enddate
111	0	2023-01-13	2023-01-17
111	1	2023-01-18	2023-01-22
111	1	2023-01-23	2023-01-23
111	1	2023-01-24	2023-01-26
111	0	2023-01-27	9999-12-31
222	0	2023-01-19	2023-01-22
222	0	2023-01-23	2023-01-27
222	0	2023-01-28	9999-12-31

I would like to transform the data in the following way:

id	status	startdate	enddate
111	0	2023-01-13	2023-01-17
111	1	2023-01-18	2023-01-26
111	0	2023-01-27	9999-12-31
222	0	2023-01-19	9999-12-31

How can I write an SQL query for this?

P.s.

SELECT '111' AS Id, 0 AS Status, '2023-01-13'::date AS StartDate, '2023-01-17'::date AS EndDate UNION
SELECT '111', 1, '2023-01-18', '2023-01-22' UNION
SELECT '111', 1, '2023-01-23',  '2023-01-23' UNION
SELECT '111', 1, '2023-01-24',  '2023-01-26' UNION
SELECT '111', 0, '2023-01-27',  '9999-12-31' UNION
SELECT '222', 0, '2023-01-19', '2023-01-22' UNION
SELECT '222', 0, '2023-01-23',  '2023-01-27' UNION
SELECT '222', 0, '2023-01-28',  '9999-12-31'

Answer 1

You want to group together consecutive rows having the same id and status - which reads like a gaps-and-islands problem.

Here is one way to do it by defining the groups with the difference of row_numberss:

select id, status, min(startdate) startdate, max(enddate) enddate
from (
    select t.*,
        row_number() over(partition by id order by startdate) rn1,
        row_number() over(partition by id, status order by startdate) rn2
    from mytable t
) t
group by id, status, rn1 - rn2
order by id, min(startdate)

Demo on DB Fiddle

id	status	startdate	enddate
111	0	2023-01-13	2023-01-17
111	1	2023-01-18	2023-01-26
111	0	2023-01-27	9999-12-31
222	0	2023-01-19	9999-12-31