CODEWITHSUNDEEP
sqlsql-server
Balaji
Balaji

Reputation: 1

SQL Query to find out Sequence in next or subsequent rows of a Table based on a condition

I have an SQL Table with following structure

Timestamp(DATETIME)|AuditEvent
---------|----------
T1|Login
T2|LogOff
T3|Login
T4|Execute
T5|LogOff
T6|Login
T7|Login
T8|Report
T9|LogOff

Want the T-SQL way to find out What is the time that the user has logged into the system i.e. Time inbetween Login Time and Logoff Time for each session in a given day.

Day (Date)|UserTime(In Hours) (Logoff Time - LogIn Time)
--------- |   -------
Jun 12    |   2
Jun 12    |   3
Jun 13    |   5

I tried using two temporary tables and Row Numbers but could not get it since the comparison was a time i.e. finding out the next Logout event with timestamp is greater than the current row's Login Event.

Upvotes: 0

Views: 395

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You need to group the records. I would suggest counting logins or logoffs. Here is one approach to get the time for each "session":

select min(case when auditevent = 'login' then timestamp end) as login_time,
       max(timestamp) as logoff_time
from (select t.*,
             sum(case when auditevent = 'logoff' then 1 else 0 end) over (order by timestamp desc) as grp
      from t
     ) t
group by grp;

You then have to do whatever you want to get the numbers per day. It is unclear what those counts are.

The subquery does a reverse count. It counts the number of "logoff" records that come on or after each record. For records in the same "session", this count is the same, and suitable for grouping.

Upvotes: 1

Related Questions