Need help on a query with analytics

From: FRITSCH Vincent <fritschvince_at_yahoo.fr>
Date: 22 Feb 2004 21:34:38 -0800
Message-ID: <b220bc5e.0402222134.3ac12a71@posting.google.com>

Hi,

i need help on a query.

I've this table :

create table t (
customer number(5),
time date,
status varchar2(1)
)
/

insert into t values (1,to_date('20030101','yyyymmdd'),1);
insert into t values (1,to_date('20030121','yyyymmdd'),0);
insert into t values (1,to_date('20030803','yyyymmdd'),1);
insert into t values (1,to_date('20030909','yyyymmdd'),1);
insert into t values (1,to_date('20031201','yyyymmdd'),0);

What i want is this output, representing periods (with a begin and an end date) of successive status.

customer   status     begin_      end_
1          1          01/01/03    21/01/03
1          0          21/01/03    03/08/03
1          1          03/08/03    01/12/03
1          0          01/12/03

On a windows 2000 platform EE 8.1.7, i write this query :

select customer,

       status,
       time begin_,
       lead(time) over (partition by customer order by time) end_

  from t
 order by customer, time
/

 CUSTOMER S BEGIN_ END_
--------- - -------- --------

        1 1 01/01/03 21/01/03
        1 0 21/01/03 03/08/03
        1 1 03/08/03 09/09/03
        1 1 09/09/03 01/12/03
        1 0 01/12/03

but this is not exactly what i search (lines 3 and 4 need to be merge)

Analytics functions can be useful here ? Can someone help me ?

Thanks in advance Received on Sun Feb 22 2004 - 23:34:38 CST