Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.cc.ukans.edu!logbridge.uoregon.edu!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail
From: "Raj" <raj.kothary@thus.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Help with joins
Date: Tue, 9 Sep 2003 23:42:46 +0100
Lines: 46
Message-ID: <bjlkss$nrs$1$8300dec7@news.demon.co.uk>
References: <bjl1ab$f8b$1$830fa79f@news.demon.co.uk> <3n2slv8mvr8b4bkrkr0ueum654olplc7bc@4ax.com>
NNTP-Posting-Host: build-111.eng.demon.net
X-Trace: news.demon.co.uk 1063147228 24444 194.217.90.111 (9 Sep 2003 22:40:28 GMT)
X-Complaints-To: abuse@demon.net
NNTP-Posting-Date: Tue, 9 Sep 2003 22:40:28 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MSMail-Priority: Normal
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:131479

[snip]
> Try:
>
> select distinct c1.USER_NAME as USER_NAME, c1.CALLED_STATION_ID, c1.TIME
as
> START_T, c2.TIME as STOP_T
> from ACCOUNTING c1, ACCOUNTING c2
> where c1.CLIENT_FUNCTION_TYPE like 'LNS'
> and c2.CLIENT_FUNCTION_TYPE like 'LNS' (+)
> and c1.USER_NAME=c2.USER_NAME (+)
> and c1.ACCT_STATUS_TYPE like 'Start'
> and c2.ACCT_STATUS_TYPE in ('Stop', NULL) (+)
> and (TO_DATE(c1.TIME,'yyyy/mm/dd HH24:mi:ss') <=
TO_DATE(c2.TIME,'yyyy/mm/dd
> HH2
> 4:mi:ss')  (+)

Just ot let you know, the outer joins on the above statement did not work.
This was the solution I ended up having to use, for your reference:

select c1.CALLED_STATION_ID, c1.USER_NAME, c1.TIME as START_T, c2.TIME as
STOP_T
from (select ACCT_SESSION_ID, CALLED_STATION_ID, TIME, USER_NAME
      from CDU_ACCOUNTING
      where CLIENT_FUNCTION_TYPE = 'LNS'
      and ACCT_STATUS_TYPE like 'Start'
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') <= to_date('2003/09/09
23:59:59','yyyy/mm/dd HH24:mi:ss')
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') >= to_date('2003/09/09
00:00:00','yyyy/mm/dd HH24:mi:ss')) c1,
     (select ACCT_SESSION_ID, TIME
      from CDU_ACCOUNTING
      where CLIENT_FUNCTION_TYPE = 'LNS'
      and ACCT_STATUS_TYPE like 'Stop'
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') >= to_date('2003/09/09
18:00:59','yyyy/mm/dd HH24:mi:ss')) c2
where c1.ACCT_SESSION_ID=c2.ACCT_SESSION_ID(+)


Thanks for taking the time to have a look though!  Is there a more efficient
way to do this?

Regards,
Raj