Re: SQL for intervals

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 02 Dec 2006 18:29:48 GMT
Message-ID: <wCjch.27664$cz.419122@ursa-nb00s0.nbnet.nb.ca>

Lennart wrote:

> Bob Badour wrote:
> [...]
>

>>select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
>>from (
>>    select id as id_lwr, t as min_t from W W1 where not exists (
>>        select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
>>    )
>>) WL,
>>(
>>    select id as id_upr, t as max_t from W W3 where not exists (
>>        select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
>>    )
>>) WU
>>where id_lwr = id_upr and max_t > min_t
>>group by id_lwr, min_t
>>;

>
> I think you will have to change the last predicate in order to detect
> things that only occur one day.
>
> /Lennart

Yep, you are right. That was a sloppy mistake on my part. I will leave fixing it as an exercise for the students at UCI. Received on Sat Dec 02 2006 - 12:29:48 CST