Andreas Mosmann wrote:
> AK schrieb am 06.06.2005 in
> <1118087102.503676.238640_at_g47g2000cwa.googlegroups.com>:
>
>> One more thing: you don't have to repeat one and the same expression
>> twice, you can use an inline view aka table expression instead:
>
>
>> select CIDUSER, CTIMESTAMP, logout_time, round((logout_time -
>> CTIMESTAMP)*24*60)
>> from(
>> 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')iv
>> CIDUSER CTIMESTAMP LOGOUT_TIME SESSION_DURATION
>> ------- --------------------- --------------------- ----------------
>> 1 12/10/2004
>> 1 12/11/2004
>> 1 12/12/2004 12/14/2004 2880
>> 1 12/15/2004
>> 1 12/16/2004 12/17/2004 1440
>> 1 12/19/2004
>> 6 rows selected
>
>
>> this way your code is easier to read and more maintainable
>
> you are right, I stopped thinking after understanding the query (it was
> 11pm), thank you, I will change this
>
> Andreas
>
Andreas,
I believe that if you would add two columns to your table, sid and serial# from
the session, your joins would become a lot simpler but would also be able to
return correct information.
Gerard
Received on Thu Jun 09 2005 - 12:44:54 CDT
