CODEWITHSUNDEEP
sqlpostgresql
Stuart
Stuart

Reputation: 130

distinct intervals for overlapping date ranges sql

I have seen lots of queries on how to merge multiple date ranges - but is there a simple query to determine all the intervals?

e.g.

start           end
2010-01-01      2010-01-31
2010-01-10      2010-02-10

I would like to display all the distinct intervals i.e.

start           end
2010-01-01      2010-01-09
2010-01-10      2010-01-31
2010-02-01      2010-02-10

The above example is for 2 date ranges - ideally the result should work for n date ranges

Upvotes: 0

Views: 186

Answers (1)

hashed tomato
hashed tomato

Reputation: 244

Check the following code.

First flatten start and end, and add "level" to each of them. The level is the number of overlapped ranges at the time. Then Generate time ranges between current and rext date for each row, omitting the rows where level = 0

with
a as (
  select "start" as date, 'S' as type from TBL
  union
  select "end" as date, 'E' as type from TBL
),
b as (
  select 
    date,
    sum(case when type = 'S' then 1 else -1 end)
        over(order by date
             rows between unbounded preceding and current row) as level,
    lead(date) over (order by  date) as next_date
  from a
)
select 
    date as start,
    (next_date - 1) as end
from b
where level > 0 // out of range
  and date <> next_date

run this:
https://www.db-fiddle.com/f/rgfTsAamaoujRaQy9FLtnQ/1

Upvotes: 1

Related Questions