Re: SQL question regarding overlapping date ranges

"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?

Probably. Here's how it could be done in Access SQL. Sybase should have equivalents for Access' IIf and DateAdd functions. IIf is more or less equivalent to CASE, while the DateAdd subtracts one second from its third argument.

select scheds.generator_number, scheds.startdate, scheds.megawatts,  iif(exists(select * from scheds as s
   where s.startdate > scheds.startdate and s.startdate <= scheds.enddate),   (select dateadd('s', -1, min(s.startdate)) from scheds as s    where s.startdate > scheds.startdate and s.startdate <= scheds.enddate),   scheds.enddate) as enddate
from scheds

> 2) If not, what is the correct data structure
> for the original data that would allow a declarative
> statement to produce the result set.

How else is this table being used?

> Any help would be appreciated.

--
Joe Foster <mailto:jfoster_at_ricochet.net>    Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!