Need help on a query with analytics

[FRITSCH Vincent]

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

[kibeha]

fr**********@yahoo.fr (FRITSCH Vincent) wrote in message news:<b2**************************@posting.google. com>...

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)

Hi

What you're saying is, that the record of 09/09/03 can be ignored, as that
record set the status of the customer to the same as it already was, right ?
Then this will give you what you want :
SQL> select
2 customer,
3 status,
4 time begin_,
5 lead(time) over (partition by customer order by time) end_
6 from (
7 select
8 t.*,
9 case
10 when status = lag(status) over (partition by customer order by
time) then 1
11 else 0
12 end samestatus
13 from t
14 )
15 where samestatus = 0
16 order by customer, time;

CUSTOMER S BEGIN_ END_
---------- - -------- --------
1 1 03-01-01 03-01-21
1 0 03-01-21 03-08-03
1 1 03-08-03 03-12-01
1 0 03-12-01
Hope that helps.

/KiBeHa

[Tony]

fr**********@yahoo.fr (FRITSCH Vincent) wrote in message news:<b2**************************@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

I think this does what you need - basically, remove "non-events" like
row 4 from the data first:

1 select customer,
2 status,
3 time begin_,
4 lead(time) over (partition by customer order by time) end_
5 from ( select customer,
6 status,
7 time,
8 lag(status) over (partition by customer order by
time) prev_status
9 from t
10 )
11 where status != nvl(prev_status,-1)
12* order by customer, time
SQL> /

CUSTOMER S BEGIN_ END_
---------- - ----------- -----------
1 1 01-JAN-2003 21-JAN-2003
1 0 21-JAN-2003 03-AUG-2003
1 1 03-AUG-2003 01-DEC-2003
1 0 01-DEC-2003

[FRITSCH Vincent]

Thank you for your help

Vincent

an******@onetel.net.uk (Tony) wrote in message news:<c0**************************@posting.google. com>...

fr**********@yahoo.fr (FRITSCH Vincent) wrote in message news:<b2**************************@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

I think this does what you need - basically, remove "non-events" like
row 4 from the data first:

1 select customer,
2 status,
3 time begin_,
4 lead(time) over (partition by customer order by time) end_
5 from ( select customer,
6 status,
7 time,
8 lag(status) over (partition by customer order by
time) prev_status
9 from t
10 )
11 where status != nvl(prev_status,-1)
12* order by customer, time
SQL> /

CUSTOMER S BEGIN_ END_
---------- - ----------- -----------
1 1 01-JAN-2003 21-JAN-2003
1 0 21-JAN-2003 03-AUG-2003
1 1 03-AUG-2003 01-DEC-2003
1 0 01-DEC-2003