Re: SQL for intervals
Date: 16 Dec 2006 04:57:37 -0800
Message-ID: <1166273857.299466.302850_at_79g2000cws.googlegroups.com>
a)
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
)
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
)
b)
, CHAR(lbound.t,USA) "LowerBound"
, W ubound
select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
from (
) WL,
(
) WU
where id_lwr = id_upr and max_t > min_t
group by id_lwr, min_t
;
SELECT lbound.id
, CHAR(MIN(ubound.t),USA) "UpperBound"
FROM W lbound
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 - 13:57:37 CET