CODEWITHSUNDEEP
sqlpostgresqldate-range
Eric Casera
Eric Casera

Reputation: 57

Selecting ranges of dates without overlapping

I have the following relational schema:

dates(date: date, code: char)

code can be ST,MN,MX,ED. An example:

╔════════════╦══════╗
║    date    ║ code ║
╠════════════╬══════╣
║ 2001-10-01 ║ ST   ║
║ 2001-10-20 ║ ST   ║
║ 2001-11-01 ║ MX   ║
║ 2001-11-01 ║ MN   ║
║ 2001-11-14 ║ MX   ║
║ 2001-11-15 ║ ED   ║
║ 2001-11-15 ║ MX   ║
║ 2001-11-27 ║ MN   ║
║ 2001-12-01 ║ ST   ║
║ 2001-12-01 ║ ED   ║
║ 2001-12-02 ║ MX   ║
║ 2001-12-03 ║ MX   ║
║ 2001-12-05 ║ ED   ║
║ 2001-12-20 ║ ST   ║
║ 2001-12-21 ║ MN   ║
║ 2001-12-24 ║ MX   ║
║ 2001-12-31 ║ ED   ║
╚════════════╩══════╝

I need to:

  1. find any range of dates that starts from one having ST as code and ends with one that has ED as a code.
  2. in those range there can't be any tuple with ST or ED as a code (the ranges can't overlap).
  3. do it without procedures and with only one SELECT statement (i can use WITH).

I did part one with the following query:

SELECT DISTINCT ON (dt.date) dt.date AS start, dt1.date AS end
FROM dates AS dt, dates AS dt1
WHERE dt.type='ST' AND dt1.type='ED' AND dt.date<dt1.date;

I can't figure out how to eliminate overlapping ranges though. Using the given example data my query outputs:

╔════════════╦════════════╗
║   start    ║    end     ║
╠════════════╬════════════╣
║ 2001-10-01 ║ 2001-12-01 ║
║ 2001-10-20 ║ 2001-11-15 ║
║ 2001-12-01 ║ 2001-12-31 ║
║ 2001-12-20 ║ 2001-12-31 ║
╚════════════╩════════════╝

As you can see the second range is overlapping with the first so it's not working as i intended.

The correct output should be:

╔════════════╦════════════╗
║   start    ║    end     ║
╠════════════╬════════════╣
║ 2001-10-20 ║ 2001-11-15 ║
║ 2001-12-20 ║ 2001-12-31 ║
╚════════════╩════════════╝

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269983

If I understand correctly, then you can use lead() and where for this purpose:

select date as startdate, next_date as enddate
from (select d.*,
             lead(code) over (order by date) as next_code,
             lead(date) over (order by date) as next_date
      from dates d
      where code in ('ST', 'ED')
     ) d
where code = 'ST' and
      next_code = 'ED';

Upvotes: 2

Related Questions