Re: finding continuous range

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

The idea is to answer queries over time periods by comparing sets of events.

2. If there is a gap between, for example, '01-JAN-01' and '20-MAR-01', then all events in the gap could be enumerated like this

select event from alldates
where '01-JAN-01' <= event and '20-MAR-01' >= event minus
select event from alldates
where '01-JAN-01' <= event and '20-MAR-01' >= event and exists (select * from schedules
where event >= startdate
and event <=enddate)

3. Build transitive closure of all intervals not having gaps:

select s1.startdate, s2.enddate from schedules s1, schedules s2 where s1.startdate < s2.enddate
and not exists (
select event from alldates
where s1.startdate <= event and s2.enddate >= event minus
select event from alldates
where s1.startdate <= event and s2.enddate >= event and exists (select * from schedules
where event >= startdate
and event <=enddate)
)

4. Find minimal cover -- select intervals that are not covered by others -- that's a standard sql exercise.

Any easier solutions?

In article <3af9ff09$0$14453$1dc6e903_at_news.corecomm.net>, Carlos Bromanski says...
>> I have a table of power schedules with start and end dates.
>> In essence it looks like this:
>>
>> table scheds
>> ( generator_number int,
>> startdate datetime,
>> enddate datetime,
>> megawatts float
>> )
>>
>> The following overlapping (by date) rows are allowed and are indeed
 typical:
>>
>> 1, 01/01/2001, 02/01/2001, 90
>> 1, 01/20/2001, 03/01/2001, 40
>> 1, 02/15/2001, 02/20/2001, 90
>>
>> 2, 12/15/2000, 01/15/2001, 25
>> 2, 01/07/2001, 06/01/2001, 105
>> 2, 01/15/2001, 01/15/2001, 140
>>
>> The users want to see schedules projected from this data
>> such that they are in the form of highest continuous schedules
>> by date range for the individual generators.
>>
>> ie: for the above data:
>>
>> 1, 01/01/2001, 02/01/2001, 90
>> 1, 02/02/2001, 02/14/2001, 40
>> 1, 02/15/2001, 02/20/2001, 90
>> 1, 02/21/2001, 03/01/2001, 40
>> 2, 12/15/2000, 01/06/2001, 25
>> 2, 01/07/2001, 01/14/2001, 105
>> 2, 01/15/2001, 01/15/2001, 140
>> 2, 01/16/2001, 06/01/2001, 105
>>
Received on Fri May 11 2001 - 20:42:02 CEST