Re: SQL question regarding overlapping date ranges

Sorry, button pressed accidentally...

Jan,

Does your query also handles continuous periods?

For example I have:

SELECT SEQNUM, STARTDATE, ENDDATE, MWT FROM SCHEDULES

---------- ---------- ---------- ----------
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:

---------- ---------- ---------- ----------
1          20-JAN-01  01-FEB-01  40
1          15-FEB-01  01-MAR-01  40

In article <9dgb2u$ios$1_at_news.tue.nl>, Jan Hidders says...
>
>1. The ones that start with an actual start date and end with an actual
> end date (of possibly another period).
>
>Note: The column names are abbreviated to gn, sd, ed, mw.
>
> SELECT s3.gn, s1.sd, s2.ed, MAX(s3.mw)
> FROM scheds AS s1 s2 s3
> WHERE s1.sd < s2.ed AND /* start before end */
> s3.sd <= s1.sd AND s3.ed >= s2.ed AND /* s3 is current */
> NOT EXISTS ( SELECT * /* no sub-period */
> FROM scheds AS s4
> WHERE (s4.sd > s1.sd AND s4.sd <= s2.ed) OR
> (s4.ed >= s1.sd AND s4.ed < s2.ed)
> )
> GROUP BY s3.gn, s1.sd, s2.ed;
>
Received on Fri May 11 2001 - 18:48:54 CEST