Re: Oracle-Advanced SQL-question

From: AK <AK_TIREDOFSPAM_at_hotmail.COM>
Date: 6 Jun 2005 06:48:56 -0700
Message-ID: <1118065736.903314.276660@g47g2000cwa.googlegroups.com>

create table event(CIDUSER number(2),
CTIMESTAMP date,
CSUBJECT char(6))
Table created

insert into event values(1, '10-dec-2004', 'login') 1 row inserted

insert into event values(1, '11-dec-2004', 'login') 1 row inserted

insert into event values(1, '12-dec-2004', 'login') 1 row inserted

insert into event values(1, '14-dec-2004', 'logout') 1 row inserted

insert into event values(1, '15-dec-2004', 'login') 1 row inserted

insert into event values(1, '16-dec-2004', 'login') 1 row inserted

insert into event values(1, '17-dec-2004', 'logout') 1 row inserted

insert into event values(1, '19-dec-2004', 'login') 1 row inserted

select CIDUSER, CTIMESTAMP,
(select min(CTIMESTAMP) from event e1 where e1.CSUBJECT='logout' and e1.CIDUSER = event.CIDUSER and e1.CTIMESTAMP > event.CTIMESTAMP and not exists(select * from event e2 where e1.CIDUSER = e2.CIDUSER and event.CTIMESTAMP < e2.CTIMESTAMP and e2.CTIMESTAMP < e1.CTIMESTAMP )
) logout_time
from event where CSUBJECT='login'
CIDUSER CTIMESTAMP LOGOUT_TIME

------- --------------------- ---------------------
      1 12/10/2004
      1 12/11/2004
      1 12/12/2004            12/14/2004
      1 12/15/2004
      1 12/16/2004            12/17/2004
      1 12/19/2004

6 rows selected    

drop table event
Table dropped Received on Mon Jun 06 2005 - 08:48:56 CDT