Re: SQL for intervals

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 16 Dec 2006 04:57:37 -0800
Message-ID: <1166273857.299466.302850@79g2000cws.googlegroups.com>

a)
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
;

b)
SELECT lbound.id

, CHAR(lbound.t,USA) "LowerBound"
, CHAR(MIN(ubound.t),USA) "UpperBound"
  FROM W lbound

, W ubound

 WHERE NOT EXISTS

       (SELECT *
          FROM 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

Compared a) and b), at least b) doesn't need "(select id as id_lwr, t as min_t from", "(select id as id_upr, t as max_t from", ") WL" and ") WU". Received on Sat Dec 16 2006 - 06:57:37 CST