I have a table that lists users who have logged in and out of a server. The table lists the PID, which is a number...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
for their ID, an event -- either logged in or logged out, and a timestamp. I threw in a small sample table below... How do I write an SQL statment to display the PID and time the user was logged in to the server?
PID Event TimeStamp 1234 Login 10:00 5678 Login 10:20 1234 Logout 11:30 5678 Logout 11:40
The trick here is to match up each login with its logout. Again, a scalar subquery in the SELECT clause comes to the rescue. But first, let's create and populate our table (note: this is Oracle flavored SQL):
create table ConnectionLog ( PID number(4), Event varchar(6), Logged date ); insert into ConnectionLog values (1234, 'Login', to_date('12-MAR-2002 10:00','DD-MON-YYYY HH24:MI')); insert into ConnectionLog values (5678, 'Login', to_date('12-MAR-2002 10:20','DD-MON-YYYY HH24:MI')); insert into ConnectionLog values (1234, 'Logout', to_date('12-MAR-2002 11:30','DD-MON-YYYY HH24:MI')); insert into ConnectionLog values (5678, 'Logout', to_date('12-MAR-2002 11:40','DD-MON-YYYY HH24:MI'));Our scalar subquery will return the earliest logout that occurs after the login for each PID. Subtract the login from the logout and we have the duration. Here's the SQL:
select PID, ( ( select min(Logged) from ConnectionLog where PID = t.PID and Logged > t.Logged ) - Logged ) * 24 * 60 as DurationInMinutes from ConnectionLog t where Event = 'Login';Notice I didn't specify that Event must be 'Logout' in the subquery. This is by design. If the session doesn't logout before a subsequent login, the subquery will act as though there is an implicit logout. That is, the next event, whether it is a login or a logout, is treated as a logout for the purpose of the subquery. Here are the results of the query:
PID DURATIONINMINUTES ---- ----------------- 1234 90 5678 80
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.