Re: SQL question regarding overlapping date ranges

In article <9dgb2u$ios$1_at_news.tue.nl>, Jan Hidders says...
>
>admin wrote:
>> 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 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.
>>
>> [...]
>>
>> 1) Is there some monstrously tricky select
>> statement that can produce the required
>> result set from the original table?
>
>It can be done in a few steps. Maybe even in one but that depends a
>little on the details of the SQL of you DBMS and I am not familiar with
>Sybase.
>
>In the first step you can compute all the elementary periods. You can
>define an elementary period as a start date and an end date such that
>a. the start date is either the start date of a real period or the end
> date + 1 of a real period,
>b. the end date is either the start date - 1 of a real period or the
> end date of a real period,
>c. the start date is before the end date, and
>d. there is no sub-period, i.e., there is no real period with a start
> date or end date in the elementary period.
>
>This means that there are four kinds of elementary periods.
>1. The ones that start with an actual start date and end with an actual
> end date (of possibly another period).
>2. The ones that start with an actual start date and end just before
> another actual start date.
>3. The ones that start just after an actual end date and end with an
> actual end date.
>4. The ones that start just after an actual end date and end just
> before an actual start date.
>
>Each type of elementary period can be computed with a single SQL
>statement:
>
>1. The ones that start with an actual start date and end with an actual
> end date (of possibly another period).
>
>Note: The column names are abbreviated to gn, sd, ed, mw.
>
> SELECT s3.gn, s1.sd, s2.ed, MAX(s3.mw)
> FROM scheds AS s1 s2 s3
> WHERE s1.sd < s2.ed AND /* start before end */
> s3.sd <= s1.sd AND s3.ed >= s2.ed AND /* s3 is current */
> NOT EXISTS ( SELECT * /* no sub-period */
> FROM scheds AS s4
> WHERE (s4.sd > s1.sd AND s4.sd <= s2.ed) OR
> (s4.ed >= s1.sd AND s4.ed < s2.ed)
> )
> GROUP BY s3.gn, s1.sd, s2.ed;
>
>The variables s1 and s2 are used as the sources of the start and
>end dates. The variable s3 is meant to be a real period in which the
>elementary period is embedded and used to derive the generator
>number en megawatts for the elementary period. Note that we derive a
>record for every actual period that is current during the elementary
>period. Finally the records are grouped to compute the one with the
>maximum number of megawatts.
>
>In a similar fashion we can compute the elementary periods of the
>second type:
>
>2. The ones that start with an actual start date and end just before
> another actual start date.
>
>You replace every occurrence of s2.ed with (s2.sd - 1) except in the
>GROUP BY clause where you replace it with s2.sd:
>
> SELECT s3.gn, s1.sd, (s2.sd - 1), MAX(s3.mw)
> FROM scheds AS s1 s2 s3
> WHERE s1.sd < (s2.sd - 1) AND
> s3.sd <= s1.sd AND s3.ed >= (s2.sd - 1) AND
> NOT EXISTS ( SELECT *
> FROM scheds AS s4
> WHERE (s4.sd > s1.sd AND s4.sd <= (s2.sd - 1)) OR
> (s4.ed >= s1.sd AND s4.ed < (s2.sd - 1))
> )
> GROUP BY s3.gn, s1.sd, s2.sd;
>
>Also for the third type of of elementary periods:
>
>3. The ones that start just after an actual end date and end with an
> actual end date.
>
>You replace in the first SQL statement s1.sd with with (s1.ed +
>1) except in the GROUP BY clause.
>
>And for the fourth type:
>
>4. The ones that start just after an actual end date and end just
> before an actual start date.
>
>You replace s1.sd with (s1.ed + 1) and s2.ed with (s1.sd - 1) except in
>the GROUP BY clause, as before.
>
>Now, after you have computed all the elementary periods you only have
>to take the union of all the results or insert them into a single
>table, and sort them by generator number and start date.
>
>Piece of cake, really. :-)
>
>--
> Jan Hidders
Received on Fri May 11 2001 - 18:33:15 CEST