Re: finding continuous range

From: mikito Harakiri <nospam_at_newsranger.com>
Date: Mon, 14 May 2001 16:50:36 GMT
Message-ID: <w1UL6.768$6j3.72743_at_www.newsranger.com>

In article <9diq28$ij1$1_at_news.tue.nl>, Jan Hidders says...
>
>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'.
>
Jan,

I rerun the sql:

SELECT SEQNUM, STARTDATE, ENDDATE, MWT FROM SCHEDULES 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

CREATE VIEW e_dates AS ( SELECT enddate FROM schedules UNION
SELECT startdate-1 enddate FROM schedules )

CREATE VIEW s_dates AS ( SELECT startdate FROM schedules UNION
SELECT enddate+1 startdate FROM schedules )

SELECT s3.seqnum, s1.startdate, s2.enddate, MAX(s3.mwt) FROM s_dates s1, e_dates s2, schedules s3

WHERE s1.startdate <= s2.enddate AND	 /* start before end */
s3.startdate <= s1.startdate AND s3.enddate >= s2.enddate AND     /* s3 is
current */
NOT EXISTS ( SELECT *                       /* no sub-period */

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 ORDER BY s3.seqnum, s1.startdate

SEQNUM STARTDATE ENDDATE MAX(S3.MWT

---------- ---------- ---------- ----------
1          01-JAN-01  19-JAN-01  40
1          20-JAN-01  01-FEB-01  40
1          02-FEB-01  05-FEB-01  40
1          15-FEB-01  19-MAR-01  40
1          20-MAR-01  20-MAR-01  40
1          21-MAR-01  25-MAR-01  40

>> Unless you would be kind to explain where second EXISTS collapsed.
 

>It doesn't. The query you cite above just computes all continuous
>periods. This is not the problem that started this thread but was
>raised by Vadim in his reply. So it solves a different but related problem.

I thought he meant that power utility report is _more complex_ than finding continuous range, in particular, as it includes finding a continous range as a subtask...

Thank you for you help offer with the paper -- I'll get back with questions. Received on Mon May 14 2001 - 18:50:36 CEST