Re: finding continuous range

Vadim Tropashko wrote:
> In article <9dhjap$6g9$1_at_news.tue.nl>, Jan Hidders says...
>
> >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?

Let's assume you have the following views:

CREATE VIEW startdates AS
  SELECT startdate
  FROM schedules;

CREATE VIEW enddates AS
  SELECT enddate
  FROM schedules;

If you ignore the fact that there are different generators you can find all the continuous periods with:

SELECT sd1.startdate, ed1.enddate
FROM startdates AS sd1, enddates AS ed1
WHERE NOT EXISTS (

             SELECT *
             FROM enddates AS ed2
             WHERE ed2.enddate BETWEEN sd1.startdate AND ed1.enddate
               AND NOT EXISTS (
                         SELECT *
                         FROM schedules AS sc
                         WHERE sc.startdate <= ed2.enddate
                           AND sc.enddate > ed2.enddate
                       )
          );

The two nested 'NOT EXISTS' make it a little hard to understand. If you reformulate it in tuple calculus it becomes more clear:

(the variables are assumed to be bound as in the SQL query)

(sd1.startdate, ed1.startdate) WHERE

    FORALL ed2 ( IF sd1.startdate < ed2.enddate < ed1.enddate

                 THEN EXISTS sc ( sc.startdate <=  ed2.enddate AND
                                  sc.enddate > ed2.enddate
                      )

In words this says that for every end date inside the period there should be a period that includes this day and ends at least one day later.

-- 
  Jan Hidders