Re: SQL for intervals

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 02 Dec 2006 15:18:26 GMT
Message-ID: <6Pgch.27577$cz.418226_at_ursa-nb00s0.nbnet.nb.ca>


Bob Badour wrote:

> 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.

P.S. Instead of just submitting one of these answers for your homework assignment at UC Irvine, do try to understand what the query is saying. Received on Sat Dec 02 2006 - 16:18:26 CET

Original text of this message