Re: SQL question regarding overlapping date ranges
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