Re: SQL question regarding overlapping date ranges

From: Joe \ <joe_at_bftsi0.UUCP>
Date: Fri, 11 May 2001 02:42:39 -0700
Message-ID: <tfnd22jmcblc3_at_corp.supernews.com>


"Joe "Nuke Me Xemu" Foster" <joe_at_bftsi0.UUCP> wrote in message <news:tfk7ds6v0s6e56_at_corp.supernews.com>...

> "admin" <admin_at_nehalem.net> wrote in message <news:tff1aijleo2r05@news.supernews.com>...
 

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

Unfortunately, as nasty as that was, it won't do the job by itself. For example, it'll leave gaps in the result when run with this example:

3, 2001-01-01, 2001-12-31, 10
3, 2001-02-01, 2001-02-10, 20
3, 2001-03-01, 2001-03-20, 30
3, 2001-03-05, 2001-03-15, 40
3, 2001-04-01, 2001-04-20, 50

--
Joe Foster <mailto:jfoster_at_ricochet.net> DC8s in Spaace: <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!
Received on Fri May 11 2001 - 11:42:39 CEST

Original text of this message