sql - Selecting ranges of dates without overlapping -


i have following relational schema:

dates(date: date, code: char) 

code can st,mn,mx,ed. 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 range of dates starts 1 having st code , ends 1 has ed code.
  2. in range there can't tuple st or ed code (the ranges can't overlap).
  3. do without procedures , 1 select statement (i can use with).

i did part 1 following query:

select distinct on (dt.date) dt.date start, dt1.date end dates dt, dates dt1 dt.type='st' , dt1.type='ed' , dt.date<dt1.date; 

i can't figure out how eliminate overlapping ranges though. using given example data 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 can see second range overlapping first it's not working intended.

the correct output should be:

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

if understand correctly, can use lead() , where purpose:

select date startdate, next_date enddate (select d.*,              lead(code) on (order date) next_code,              lead(date) on (order date) next_date       dates d       code in ('st', 'ed')      ) d code = 'st' ,       next_code = 'ed'; 

Comments

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

jquery - Clone last and append item to closest class -

c - Unrecognised emulation mode: elf_i386 on MinGW32 -