Re: SQL for intervals
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 02 Dec 2006 15:06:07 GMT
Message-ID: <zDgch.27576$cz.418051_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> Heres one attempt, more elegant solutions probably exists. I assume you
> mean dates when saying timestamps (it is a datatype in sql). Anyhow
> here it goes:
>
> 1. Generate a calendar table. If this is a common task you should
> probably generate a permanent one. In the example I use a common table
> expression.
>
> with cal (id, t) as (
> select id, min(t) from W group by id
> union all
> select id, t + 1 day from cal
> where (t + 1 day) <= (select max(t) from w where id = cal.id)
>
> 2. Calculate the complement to your dates in the w table
>
> ), compl (id, t) as (
> select * from cal
> where not exists (
> select 1 from w
> where id = cal.id and t = cal.t
> )
> union all
> select id, min(t) - 1 day from cal group by id
> union all
> select id, max(t) + 1 day from cal group by id
>
> I added an start_date and an end_date
>
> 3. Determine upper bound for each interval
>
> ), upper (id, min_date, max_date) as (
> select w.id, w.t, min(c.t) - 1 day from compl c, w
> where c.t > w.t
> group by w.id, w.t
> )
>
> 4. Finally, determine lower bound for each row in upper
>
> select id, min(min_date), max_date from upper
> group by id, max_date;
>
> So, we end up with an expression like:
>
> with cal (id, t) as (
> select id, min(t) from W group by id
> union all
> select id, t + 1 day from cal
> where (t + 1 day) <= (select max(t) from w where id = cal.id)
>
> ), compl (id, t) as (
> select * from cal
> where not exists (
> select 1 from w
> where id = cal.id and t = cal.t
> )
> union all
> select id, min(t) - 1 day from cal group by id
> union all
> select id, max(t) + 1 day from cal group by id
> ), upper (id, min_date, max_date) as (
> select w.id, w.t, min(c.t) - 1 day from compl c, w
> where c.t > w.t
> group by w.id, w.t
> )
> select id, min(min_date), max_date from upper
> group by id, max_date;
>
> If your dbms does not support common table expression you can use views
> for sub calculations
>
>
> HTH
> /Lennart
Date: Sat, 02 Dec 2006 15:06:07 GMT
Message-ID: <zDgch.27576$cz.418051_at_ursa-nb00s0.nbnet.nb.ca>
Lennart 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. >>
>
>
> Heres one attempt, more elegant solutions probably exists. I assume you
> mean dates when saying timestamps (it is a datatype in sql). Anyhow
> here it goes:
>
> 1. Generate a calendar table. If this is a common task you should
> probably generate a permanent one. In the example I use a common table
> expression.
>
> with cal (id, t) as (
> select id, min(t) from W group by id
> union all
> select id, t + 1 day from cal
> where (t + 1 day) <= (select max(t) from w where id = cal.id)
>
> 2. Calculate the complement to your dates in the w table
>
> ), compl (id, t) as (
> select * from cal
> where not exists (
> select 1 from w
> where id = cal.id and t = cal.t
> )
> union all
> select id, min(t) - 1 day from cal group by id
> union all
> select id, max(t) + 1 day from cal group by id
>
> I added an start_date and an end_date
>
> 3. Determine upper bound for each interval
>
> ), upper (id, min_date, max_date) as (
> select w.id, w.t, min(c.t) - 1 day from compl c, w
> where c.t > w.t
> group by w.id, w.t
> )
>
> 4. Finally, determine lower bound for each row in upper
>
> select id, min(min_date), max_date from upper
> group by id, max_date;
>
> So, we end up with an expression like:
>
> with cal (id, t) as (
> select id, min(t) from W group by id
> union all
> select id, t + 1 day from cal
> where (t + 1 day) <= (select max(t) from w where id = cal.id)
>
> ), compl (id, t) as (
> select * from cal
> where not exists (
> select 1 from w
> where id = cal.id and t = cal.t
> )
> union all
> select id, min(t) - 1 day from cal group by id
> union all
> select id, max(t) + 1 day from cal group by id
> ), upper (id, min_date, max_date) as (
> select w.id, w.t, min(c.t) - 1 day from compl c, w
> where c.t > w.t
> group by w.id, w.t
> )
> select id, min(min_date), max_date from upper
> group by id, max_date;
>
> If your dbms does not support common table expression you can use views
> for sub calculations
>
>
> HTH
> /Lennart
Lennart, is it my imagination or do you have a fetish for recursive queries? Received on Sat Dec 02 2006 - 16:06:07 CET