By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

Complex query

P: n/a
Hi,

i need some 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 :

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.

Can someone help me ?

Thanks in advance

Vincent
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.