Re: SQL question regarding overlapping date ranges

Very interesting problem...
Darn it, I left my Celko "SQL For Smarties" at work, I think it has some suggestions regarding "sequences and runs"... Maybe you could try using a table of calendar days, one row per day for all days from 1/1/1995 to 12/31/2050 or whatever. That table joined to your table scheds may allow you to discover continuous sequences of days that have the highest wattage.
Just brainstorming...
- cb

admin <admin_at_nehalem.net> wrote in message news:tff1aijleo2r05_at_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:
>
> 1) Is there some monstrously tricky select
> statement that can produce the required
> result set from the original table?
>
> 2) 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 Thu May 10 2001 - 04:40:03 CEST