Re: Oracle-Advanced SQL-question

Andreas Mosmann wrote:

> 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
>

[...]
> 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
>

[...]

AK's solution is clean and elegantly presented, so if it meets your needs, great (but be sure to do some performance testing!)

Having dealt with a similar problem, and reading your desired output above, I think you might have an additional, more subtle requirement. To wit, you want to distinguish between state="broken" (they logged out, timed out, closed their app window, whatever, and no LOGOUT record was, or will ever be, created) and state="still at work" (there is no logout record because they are actually still logged in, and a logout record could still be created for this session).

My solution, using Perl, was to read through the records and make an (adjustable) assumption about the maximum length of a login session. With a little sensitivity analysis, I was able to come up with a number that resulted in as much under-counting as over-counting of sessions currently active, which allowed for a statistically-acceptable, histogram-style report of active users for every hour throughout the business day (this app had a definite 24-hour usage cycle, with user activity going from zero at the start of the day to non-zero and then back to zero by the end of the day).

-Mark Bole Received on Mon Jun 06 2005 - 10:09:37 CDT