Re: SQL question regarding overlapping date ranges

Mikito Harakiri wrote:
>
> Does your query also handles continuous periods?

Yes, it does.

> For example I have:
>
> SELECT SEQNUM, STARTDATE, ENDDATE, MWT FROM SCHEDULES
>
> ---------------------------------------------------------
> SEQNUM STARTDATE ENDDATE MWT
> ---------- ---------- ---------- ----------
> 1 01-JAN-01 01-FEB-01 40
> 1 20-JAN-01 01-MAR-01 40
> 1 15-FEB-01 20-MAR-01 40
>
> Your query
>
> SELECT s3.seqnum, s1.startdate, s2.enddate, MAX(s3.mwt)
> FROM schedules s1, schedules s2, schedules s3
> WHERE s1.startdate < s2.enddate AND
> s3.startdate <= s1.startdate AND s3.enddate >= s2.enddate
> AND NOT EXISTS ( SELECT *
> FROM schedules s4
> WHERE (s4.startdate > s1.startdate AND s4.startdate <= s2.enddate) OR
> (s4.enddate >= s1.startdate AND s4.enddate < s2.enddate)
> )
> GROUP BY s3.seqnum, s1.startdate, s2.enddate
>
> returns only:
>
> ---------------------------------------------------------
> SEQNUM STARTDATE ENDDATE MAX(S3.MWT
> ---------- ---------- ---------- ----------
> 1 20-JAN-01 01-FEB-01 40
> 1 15-FEB-01 01-MAR-01 40
That's right. This query only retrieves only one of the four types of elementary periods. You also need to execute the other three queries I described to get all of them. Note that they are very similar to the first one, so you can write them quickly with some cut and paste.

Or you can use the simpeler solution I gave in my reply to myself. You probably have seen it by now. My apologies for making things more complicated then they have to be.

-- 
  Jan Hidders