SQL question regarding overlapping date ranges

From: admin <admin_at_nehalem.net>
Date: Mon, 7 May 2001 22:34:10 -0700
Message-ID: <tff1aijleo2r05@news.supernews.com>

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
  )

A trigger enforces that startdate <= enddate on inserts and updates. I actually have data to the hour and minute but have left it out for the sake of clarity.

The following overlapping (by date) rows are allowed and are indeed typical:

1, 01/01/2001, 02/01/2001, 90
1, 01/20/2001, 03/01/2001, 40
1, 02/15/2001, 02/20/2001, 90

2, 12/15/2000, 01/15/2001, 25

2, 01/07/2001, 06/01/2001, 105
2, 01/15/2001, 01/15/2001, 140

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.

ie: for the above data:

1, 01/01/2001, 02/01/2001, 90
1, 02/02/2001, 02/14/2001, 40
1, 02/15/2001, 02/20/2001, 90
1, 02/21/2001, 03/01/2001, 40
2, 12/15/2000, 01/06/2001, 25
2, 01/07/2001, 01/14/2001, 105
2, 01/15/2001, 01/15/2001, 140
2, 01/16/2001, 06/01/2001, 105

The DBMS is Sybase. I have produced the correct output via Transact-SQL but the looping and temp variables look more like Fortran than SQL and it has piss poor slow execution time as it deals with hundreds of generators and thousands of schedules..

My questions are two:

Is there some monstrously tricky select statement that can produce the required result set from the original table?
If not, what is the correct data structure for the original data that would allow a declarative statement to produce the result set.

Any help would be appreciated. Received on Tue May 08 2001 - 00:34:10 CDT