Re: SQL question regarding overlapping date ranges

Jan Hidders wrote:
> 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:

Actually you can also just precompute all the possible start and end dates, and then compute everything in one SQL statements. First, you determine the potential start dates:

  ( SELECT sd
    FROM sched )
  UNION
  ( SELECT ed - 1 AS sd (*)
    FROM sched )

and the potential end dates:

  ( SELECT ed
    FROM sched )
  UNION
  ( SELECT sd - 1 AS ed
    FROM sched )

(*) The syntax for renaming the column may vary. I don't know the proper syntax for Sybase.

If you put these in the tables 's_dates' and 'e_dates', respectively, you can compute the end result as follows:

   SELECT s3.gn, s1.sd, s2.ed, MAX(s3.mw)    FROM s_dates AS s1, e_dates AS s2, scheds AS 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)
                )

Maybe Sybase even allows you to combine this into one statement.

-- 
  Jan Hidders