Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL for intervals

Re: SQL for intervals

From: Lennart <Erik.Lennart.Jonsson_at_gmail.com>
Date: 2 Dec 2006 05:19:41 -0800
Message-ID: <1165062914.371294.135000@79g2000cws.googlegroups.com>

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 Received on Sat Dec 02 2006 - 07:19:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US