Question 89243 in PostgreSQL Question posted in
The official documentation can be found here.

SQL: Merge almost identical lines with start_date and end_date – Postgresql

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'
Tags:

2

Answers


  1. 0

    I think you are looking for something like this :

    select id, status, min(startdate), max(enddate)
from mytable
group by id, status, date_format(startdate, '%Y-%m'), date_format(enddate, '%Y-%m')

    Result :

    id  status  min(startdate)  max(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  2023-01-27
222 0   2023-01-28  9999-12-31
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search