Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g47g2000cwa.googlegroups.com!not-for-mail From: "AK" Newsgroups: comp.databases.oracle.misc Subject: Re: Oracle-Advanced SQL-question Date: 6 Jun 2005 06:48:56 -0700 Organization: http://groups.google.com Lines: 59 Message-ID: <1118065736.903314.276660@g47g2000cwa.googlegroups.com> References: <1118062535.89@user.newsoffice.de> NNTP-Posting-Host: 205.156.188.254 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1118065742 14000 127.0.0.1 (6 Jun 2005 13:49:02 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 6 Jun 2005 13:49:02 +0000 (UTC) In-Reply-To: <1118062535.89@user.newsoffice.de> User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g47g2000cwa.googlegroups.com; posting-host=205.156.188.254; posting-account=dcyjXg0AAAAAr7KEmYS5ttp6D-QeNceH Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:119652 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