Re: SQL for intervals

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 11 Dec 2006 23:18:45 -0800
Message-ID: <1165907925.619089.264790_at_73g2000cwn.googlegroups.com>

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
> ;
>
> Depending on your flavour of SQL, you might have to change the simple
> additions to function calls like DATE_ADD etc.
Same kind of thinking. But it is not necessary to use nested table expression.
SELECT lbound.id

     , CHAR(lbound.t,USA)      "LowerBound"
     , CHAR(MIN(ubound.t),USA) "UpperBound"
  FROM W lbound
     , W ubound
 WHERE NOT EXISTS
       (SELECT *
          FROM W lower
         WHERE lower.id = lbound.id
           AND lower.t  = lbound.t - 1 day
       )

   AND ubound.id = lbound.id
   AND ubound.t >= lbound.t
   AND NOT EXISTS

       (SELECT *
          FROM W upper
         WHERE upper.id = lbound.id
           AND upper.t  = ubound.t + 1 day
       )
 GROUP BY
       lbound.id, lbound.t

Received on Tue Dec 12 2006 - 08:18:45 CET