Re: SQL for intervals

suzan.james_at_gmail.com wrote:
> Hi All,
>
> I just start learning sql. I wonder whether you can help me with this:
> I have a table W for a warehouse in which each item has a
> unique id and a timestamp t.
> Each record in the table is an id and the time stamp shows the day the
> item with that id was in the warehouse. One record can be
> ('123','1-1-1990') means item with id '123' is in the warehouse at date
> '1-1-1990'
> . In this table I want to find all the
> intervals for which an item is present in the table. Assume
> item id 123 is in the warehouse with these timestampe:
> ('123','1-1-1990'),('123','1-2-1990'),('123','1-3-1990'),('123','1-4-1990'),
> ('123','1-10-1990'),('123','1-11-1990'). Notice there is a gap between
> '1-4-1990 and '1-10-1990'
> The outptut intervals I want for '123' are
> ('123','1-1-1990','1-4-1990') and
> ('123','1-10-1990','1-11-1990')
> The first one means that '123' was in the warehouse from date
> '1-1-1990' to '1-4-1990' (continuously with no gap)
> and the second one means the item was also in the warehouse from
> '1-10-1990' to '1-11-1990' (continuously without gap)
>
> I want to find all such intervals for each item.
>
> Thank.
>

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. Received on Sat Dec 02 2006 - 16:04:20 CET