Re: finding continuous range
Date: 12 May 2001 07:51:36 GMT
Message-ID: <9diq28$ij1$1_at_news.tue.nl>
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...
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 HiddersReceived on Sat May 12 2001 - 09:51:36 CEST