CODEWITHSUNDEEP
sqlt-sql
byrak79
byrak79

Reputation: 61

Total Time Logged Into the Program SQL

I have a table that has login and logout times of users measured in the same column. The table looks like this:

offset    eventtimeutc                userID     propertyname 
-18000    2021-05-15 12:00:00.000     Jane Doe     login
-18000    2021-05-15 12:10:00.000     John Doe     login
-18000    2021-05-15 13:19:00.000     Jane Doe     logout
-18000    2021-05-15 13:25:00.000     Jane Doe     login
-18000    2021-05-15 18:00:00.000     Jane Doe     logout
-18000    2021-05-15 18:05:00.000     John Doe     logout

I am trying to calculate how much time a user spends in the program between each login and logout. So for example, Jane Doe spends 1 hour 19 minutes between first login and logout, and then another 4 hours and 40 minutes in the next. I tried converting login and logout into two separate columns with a CASE WHEN propertyname = 'login' then eventtimeutc else null end as Login statement and repeat the same for logout, but the result I get is a NULL value in logout column when login is populated and a NULL in login when logout is populated. Is there a better and faster way for me to calculate how much time a user spends logged into the program in each instance? Thanks for the help

Upvotes: 0

Views: 244

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If your data is perfectly interleaved (each login is followed by a logout and each logout is followed by a login), you can just use lead() and some date arithmetic:

select userId,
       sum(datediff(second, eventtimeutc, next_eventtimeutc)) as total_seconds
from (select t.*,
             lead(eventtimeutc) over (partition by userId order by eventtimeutc) as next_eventtimeutc
      from t
     ) t
where propertyname = 'login'
group by userId;

If the original condition is not true (so you could have two logins in a row or two logouts in a row), then I would suggest that you ask a new question with appropriate sample data and desired results and a clear explanation of how to handle the adjacencies.

Upvotes: 0

Thom A
Thom A

Reputation: 95896

This is effectively a gaps and island problem. You can get the data into groups is by using a windowed conditional aggregate to count the number of logins so far; each login increments the number by 1, putting the values into groups. Then you can use some additional conditional aggregation to pivot the data to get the different in the login and logout times. I add both a value for seconds and time (note that if you could have values 24 hours or more, that will error, and I suggest you just stick to seconds):

CREATE TABLE dbo.YourTable(offset int,
                           eventtimeutc datetime,
                           userID sysname,
                           propertyname varchar(6));
GO
INSERT INTO dbo.YourTable
VALUES(-18000,'2021-05-15T12:00:00.000',N'Jane Doe','login'),
      (-18000,'2021-05-15T12:10:00.000',N'John Doe','login'),
      (-18000,'2021-05-15T13:19:00.000',N'Jane Doe','logout'),
      (-18000,'2021-05-15T13:25:00.000',N'Jane Doe','login'),
      (-18000,'2021-05-15T18:00:00.000',N'Jane Doe','logout'),
      (-18000,'2021-05-15T18:05:00.000',N'John Doe','logout');
GO
WITH Grps AS(
    SELECT eventtimeutc,
           userID,
           propertyname,
           COUNT(CASE propertyname WHEN 'login' THEN 1 END) OVER (PARTITION BY userID ORDER BY eventtimeutc
                                                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PropertyGrp
    FROM dbo.YourTable)
SELECT userID,
       MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END) AS LoginStart,
       DATEDIFF(SECOND,MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END),MAX(CASE propertyname WHEN 'logout' THEN eventtimeutc END)) AS LoginSeconds,
       DATEADD(SECOND,DATEDIFF(SECOND,MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END),MAX(CASE propertyname WHEN 'logout' THEN eventtimeutc END)),CONVERT(time,'00:00')) AS LoginDuration
FROM Grps
GROUP BY UserID,
         PropertyGrp
ORDER BY LoginStart ASC;

GO
DROP TABLE dbo.YourTable;
GO

Upvotes: 1

Related Questions