Re: finding continuous range

Mikito Harakiri wrote:
> I apologyse for replying to wrong message: my newsreader displays
> only a subset of google.
>
> Given the data:
>
> SEQNUM STARTDATE ENDDATE MWT
> ---------- ---------- ---------- ----------
> 1 01-JAN-01 01-FEB-01 40
> 1 20-JAN-01 05-FEB-01 40
> 1 15-FEB-01 20-MAR-01 40
> 1 20-MAR-01 25-MAR-01 40
>
> Your query returns:
>
> SEQNUM STARTDATE ENDDATE MAX(S3.MWT
> ---------- ---------- ---------- ----------
> 1 01-JAN-01 19-JAN-01 40
> 1 20-JAN-01 01-FEB-01 40
> 1 31-JAN-01 01-FEB-01 40
> 1 04-FEB-01 05-FEB-01 40
> 1 15-FEB-01 19-MAR-01 40
> 1 24-MAR-01 25-MAR-01 40
>
> I run the one that you suggested the second time (with two auxiliary
> views). I don't see interval (01-JAN-01, 05-FEB-01) in the output...

Oops. The 'ed - 1' in the 's-dates' view should have been 'ed + 1' because it is of course the date just *after* an end date that is the potential start date of an elementary period. So you get:

  ( SELECT sd
    FROM sched )
  UNION
  ( SELECT ed + 1 AS sd
    FROM sched )

And there is another small mistake as well. Since periods may also consist of just one day, the condition 's1.sd < s2.ed' in the first WHERE clause should be replaced with 's1.sd <= s2.ed'.

Sorry for not testing my SQL but I don't have a database at hand at the moment.

-- 
  Jan Hidders