Re: finding continuous range

In article <9dhjap$6g9$1_at_news.tue.nl>, Jan Hidders says...
>
>Vadim Tropashko wrote:
>> Assuming that schedule covers periods like this
>>
>> 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
>>
>> finding continuous periods
>>
>> STARTDATE ENDDATE
>> ---------- ----------
>> 01-JAN-01 05-FEB-01
>> 15-FEB-01 20-MAR-01
>>
>> IMHO is a quite a challenge itself. Here is one solution
>>
>> 1. Create a simplistic timeline of events. Basically, we are
>> interested in startdate, enddate and some dates in-between. Below is
>> somewhat expensive vay to create this timeline. More optimal would be
>> just adding midpoints between neibouring dates.
>>
>> VIEW alldates AS
>> select startdate event from schedules
>> union
>> select enddate event from schedules
>> union
>> select a.startdate + (b.startdate-a.startdate)/2 event
>> from schedules a, schedules b where b.startdate > a.startdate
>> union
>> select a.enddate + (b.enddate-a.enddate)/2 event
>> from schedules a, schedules b where b.enddate > a.enddate
>> union
>> select a.startdate + (b.enddate-a.startdate)/2 event
>> from schedules a, schedules b where b.enddate > a.startdate
>> union
>> select a.enddate + (b.startdate-a.enddate)/2 event
>> from schedules a, schedules b where b.startdate > a.enddate
>
>I think you can simplify this because you only need the start and end
>dates.

Jan,

I changed alldates view to just

select startdate event from schedules
union
select enddate event from schedules

and transitive closure of

STARTDATE ENDDATE

---------- ----------
01-JAN-01  01-FEB-01
20-JAN-01  05-FEB-01
15-FEB-01  20-MAR-01
20-MAR-01  25-MAR-01

is now incorrectly becomes

STARTDATE ENDDATE

---------- ----------
01-JAN-01  01-FEB-01
20-JAN-01  01-FEB-01
01-JAN-01  05-FEB-01
20-JAN-01  05-FEB-01
01-JAN-01  20-MAR-01
20-JAN-01  20-MAR-01
15-FEB-01  20-MAR-01
01-JAN-01  25-MAR-01   *** wrong ***
20-JAN-01  25-MAR-01
15-FEB-01  25-MAR-01
20-MAR-01  25-MAR-01

>This is because a continuous period can be defined as a start
>date sd1 and a begin date bd1 such that every end date ed2
>between them is also between the begin and end date of some period.

You probably mean some other approach. Could you reformulate your idea in calculus, at least, please? Received on Fri May 11 2001 - 23:41:56 CEST