Re: finding continuous range

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. 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.

-- 
  Jan Hidders