Postgres most efficient way to split overlapping daterange

user_2585154 0
Viewed 55

For example I have following entities in table A:

entityId, metaData, startDate, endDate
Row 1: 1, some content, 2021-01-01, 2022-01-01
Row 2: 1, some content 2, 2022-01-02, 9999-12-31

And I have following entities in table B:

entityId, metaData, startDate, endDate
Row 1: 1, overlapping content, 2021-08-12, 2022-01-01
Row 2: 1, overlapping content 2, 2022-03-01, 2023-01-01
Row 3: 1, overlapping content 3, 2023-01-02, 9999-12-31

|-----------ROW 1 (A)-----------| |-----------ROW 2 (A)------------| 
        |-------ROW 1 (B)-------|   |--ROW 2 (B)--|  |--ROW 3 (B)--|

As output I want to split rows from A by overlapping with rows from B (using entityId key):

Row 1 (A): 1, some content, 2021-01-01, 2021-08-11
Row 1 (B): 1, overlapping content, 2021-08-12, 2022-01-01
Row 2 (A): 1, some content 2, 2022-01-02, 2022-02-28
Row 2 (B): 1, overlapping content 2, 2022-03-01, 2023-01-01
Row 3 (B): 1, overlapping content 3, 2023-01-02, 9999-12-31

P.S. Rows from A/B can follow each other in a non continuous date range. For example:

Row 1: 1, content, 2021-01-01 2021-12-01
Row 2: 1, content 2, 2021-12-31, 9999-12-31
overlap date postgresql sql split
1 Answers

Does smth like this fit your needs?

with cte as
(
    select * from table_a
    union all
    select * from table_b
)
select
       entityid,
       metadata,
       startdate,
       coalesce(
           ((lead(startdate) over (order by startdate)) - INTERVAL '1 DAY') :: DATE,
           enddate
       )
from cte order by startdate
user_16849210
Related