Oracle-Advanced SQL-question

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Mon, 06 Jun 2005 14:55:35 +0200
Message-ID: <1118062535.89@user.newsoffice.de>

Hi,

I have a table that contains 3 columns,

CIDUSER
CTIMESTAMP
CSUBJECT and everytime user logs into database with a special application the app creates a record
f.e.

17     2005-06-06     LOGIN
17     2005-06-06     LOGOUT

if all is usual there are as many LOGIN- entries as LOGOUT-entries for every user that is not actual in the database. for all users in Database there should be an Entry LOGIN more than LOGOUT But if there happened any problems with the application (that I have to debug - that is one of the reasons for that query) the LOGOUT- Entry could be missed, so that in DB could be that situation:

17     2005-06-06 15:00    LOGIN
17     2005-06-06 16:00    LOGOUT
17     2005-06-06 17:00    LOGIN
17     2005-06-06 18:00    LOGIN
17     2005-06-06 19:00    LOGOUT
17     2005-06-06 20:00    LOGIN
17     2005-06-06 21:00    LOGOUT
17     2005-06-06 22:00    LOGIN

Now the question: I am looking for a statement, that gives me something like

USER   LOGIN             LOGOUT                TIME        STATE
17     2005-06-06 15:00  2005-06-06 16:00      01:00       complete
17     2005-06-06 17:00  NULL                  NULL        broken
17     2005-06-06 18:00  2005-06-06 19:00      01:00       complete
17     2005-06-06 20:00  2005-06-06 21:00      01:00       complete
17     2005-06-06 22:00  NULL                  00:35       still at work

I hope I explained it understandable.
I experimented with something like

select

  I.CIDUSER,
  I.CTIMESTAMP,
  O.CTIMESTAMP,
  O.CTIMESTAMP-I.CTIMESTAMP as OnTime,

from
(

  select
    *,
    rownum as Line
  from
  ( select

      CIDUSER,
      CTIMESTAMP,
      CSUBJECT
    from
      TBLOG
    where
      CSUBJECT='LOGIN'
    order by
      CIDUSER,
      CTIMESTAMP

  )
) I
join
(

  select
    *,
    rownum as Line
  from
  ( select

      CIDUSER,
      CTIMESTAMP,
      CSUBJECT
    from
      TBLOG
    where
      CSUBJECT='LOGOUT'
    order by
      CIDUSER,
      CTIMESTAMP

  )
) O
on
  I.Line=O.Line

this will work as long as all user completed theis sessions usual. And I dont get the state.

But what to do in the special cases I mentioned above?

Many thanks for your help

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Jun 06 2005 - 07:55:35 CDT