Re: SQL question regarding overlapping date ranges
Date: 11 May 2001 09:23:42 GMT
Message-ID: <9dgb2u$ios$1_at_news.tue.nl>
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.
actual end date.
before an actual start date.
Each type of elementary period can be computed with a single SQL
statement:
3. The ones that start just after an actual end date and end with an
4. The ones that start just after an actual end date and end just
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 HiddersReceived on Fri May 11 2001 - 11:23:42 CEST