Re: finding continuous range

mikito Harakiri wrote:
>
> I rerun the sql:

Great! It worked. Thanks for letting me know. :-)

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

Yes, that is what Vadim meant. But finding the maximal continuous ranges isn't really that hard. It is, for instance, easy to determine their begin dates:

CREATE VIEW cont_start AS
  SELECT s1.startdate
  FROM schedules AS s1
  WHERE NOT EXISTS (

              SELECT *
              FROM schedules AS s2
              WHERE s2.startdate < s1.startdate
                AND s2.enddate >= s1.startdate
            );

Note that this view simply determines the begin dates for which there is not a period that allows the range to start earlier.

In a similar fashion you can determine the end dates of the maximal continuous ranges:

CREATE VIEW cont_end AS
  SELECT s1.enddate
  FROM schedules AS s1
  WHERE NOT EXISTS (

              SELECT *
              FROM schedules AS s2
              WHERE s2.startdate <= s1.enddate
                AND s2.enddate > s1.enddate
            );

And finally you can combine these two to find the maximal continuous periods:

SELECT begindate, MIN(enddate)
FROM cont_start, cont_end
WHERE begindate <= enddate
GROUP BY begindate;

Amazing, isn't it. :-) Extending this so it works for several generators is left to the reader as an exercize.

> Thank you for you help offer with the paper -- I'll get back with questions.

Ok. I'll be looking forward to them although I'm a bit timepressed at the moment because my thesis has to be ready at the end of this montth.

-- 
  Jan Hidders