Help - Contiguous Date Range Query

gumper · #1 (**permalink**) 11-10-03, 10:01

I have a table that contains (among other things) start and end range datetimes. The ranges can overlap. View these datetimes as segments of coverage on a number line. Rather than returning each line segment individually, I'd like the query to return the start and the end of the contiguous segments. For example, the desired output of the query on the below table would be:

/* Start SQL */

/* Desired output: range_start_datetime range_end_datetime
01/01/2003 01/06/2003
01/08/2003 01/12/2003
*/

create table #ranges (range_start_datetime datetime, range_end_datetime datetime)

/* Range 1: 01/01/2003 to 01/06/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/01/2003', '01/02/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/03/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/02/2003', '01/06/2003')

/* Gap - no data at 01/07/2003 */

/* Range 2: 01/08/2003 to 01/12/2003 */
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/08/2003', '01/09/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/09/2003', '01/10/2003')
insert into #ranges(range_start_datetime, range_end_datetime) values ('01/10/2003', '01/12/2003')

select * from #ranges
drop table #ranges

/* End SQL */

andrewst · #2 (**permalink**) 11-11-03, 11:26

This version will work in Oracle at least:

Code:

SQL> select range_start_datetime,
  2         (
  3           select min(range_end_datetime)
  4           from   ranges r3
  5           where not exists
  6           ( select range_start_datetime from ranges r4
  7             where r4.range_start_datetime <= r3.range_end_datetime
  8             and   r4.range_end_datetime > r3.range_end_datetime
  9           )
 10           and r3.range_end_datetime >= r1.range_start_datetime
 11         )
 12  from ranges r1
 13  where not exists
 14  ( select range_end_datetime from ranges r2
 15    where r2.range_end_datetime >= r1.range_start_datetime
 16    and   r2.range_start_datetime < r1.range_start_datetime
 17  );

RANGE_STAR (SELECTMIN
---------- ----------
01/01/2003 01/06/2003
01/08/2003 01/12/2003

gumper · #3 (**permalink**) 11-11-03, 12:39

This works in Sybase as well. I had to change the first select to distinct when I added a few more test cases, but it works! I'm impressed with your skills.

If you get a moment, could you explain what your query is doing? I didn't even consider a 4-join solution.

Gump

Quote:

Originally posted by andrewst
This version will work in Oracle at least:

Code:

SQL> select range_start_datetime,
  2         (
  3           select min(range_end_datetime)
  4           from   ranges r3
  5           where not exists
  6           ( select range_start_datetime from ranges r4
  7             where r4.range_start_datetime <= r3.range_end_datetime
  8             and   r4.range_end_datetime > r3.range_end_datetime
  9           )
 10           and r3.range_end_datetime >= r1.range_start_datetime
 11         )
 12  from ranges r1
 13  where not exists
 14  ( select range_end_datetime from ranges r2
 15    where r2.range_end_datetime >= r1.range_start_datetime
 16    and   r2.range_start_datetime < r1.range_start_datetime
 17  );

RANGE_STAR (SELECTMIN
---------- ----------
01/01/2003 01/06/2003
01/08/2003 01/12/2003

andrewst · #4 (**permalink**) 11-11-03, 12:52

Probably easiest to start from here:

Code:

select range_start_datetime,
from ranges r1
where not exists
( select range_end_datetime from ranges r2
  where r2.range_end_datetime >= r1.range_start_datetime
  and   r2.range_start_datetime < r1.range_start_datetime
);

All I have done is remove the second column from the main select (which was a "scalar subquery").

This query gets the start of each contiguous range, by finding all the start dates for which there does not exist a preceding record that meets or overlaps it, i.e.:

RANGE_STAR
----------
01/01/2003
01/08/2003

Now let's look at the scalar subquery:

Code:

select min(range_end_datetime)
from   ranges r3
where not exists
( select range_start_datetime from ranges r4
  where r4.range_start_datetime <= r3.range_end_datetime
  and   r4.range_end_datetime > r3.range_end_datetime
)
and r3.range_end_datetime >= r1.range_start_datetime

This sort of does the opposite of the query above: it finds the end of each contiguous range, i.e. all the end dates for which there does not exist a following record that meets or overlaps it, i.e.

Code:

select range_end_datetime
from   ranges r3
where not exists
( select range_start_datetime from ranges r4
  where r4.range_start_datetime <= r3.range_end_datetime
  and   r4.range_end_datetime > r3.range_end_datetime
)

RANGE_END_
----------
01/06/2003
01/12/2003

It then finds the MIN of those end dates where the end_date is on or after the start date of each record in the main query (i.e. corellated): for start date 01/01/2003 that will be 01/06/2003, and for start date 01/08/2003 that will be 01/12/2003.

Does that make sense?

Internet Services

Web Development

Digital Marketing

Consumer Tech