Problem solve
Get help with specific problems with your technologies, process and projects.
Displaying time ranges based on login, logout
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 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 [email protected] 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.
