Sql-server – Query to get total time logged in per week

sql serversql-server-2008-r2

I need to find the number of hours each user is logged into an application per week. The table structure is as follows:

[ID]
[UserID]
[Time] (datetime field)
[EventType] ("login" or "logout")

There can be many logins/logouts per day. Since it would probably be significantly easier to get the first login and last logout of the day, that is also acceptable (although less accurate).

Any suggestions where to start? I'll probably have to do some partitioning, right?

The table logs the launch of a desktop application. The application will time out on its own without activity. Launching another instance of the application will log out your active session and start a new one, so I don't think there could be an overlap. I do not have control over the data types as they are part of the application. EventType is a string.

Best Answer

There's probably a more elegant solution but using your criteria from your comment to @Erik that the logout will be recorded this should get you what you need, or very close:

DECLARE @UserID sysname;
DECLARE @LastUserID sysname;
DECLARE @Time datetime;
DECLARE @EventType varchar(10);
DECLARE @CurrentSessionRowID int;

CREATE TABLE #results (
        ID int IDENTITY
               PRIMARY KEY
      , UserID sysname
      , LoginTime datetime
      , LogoutTime datetime
      , [Duration(Hours)] AS DATEDIFF(mi, LoginTime, LogoutTime) / 60.  --  datediff by minutes so partial hours can be displayed in the final results
       );


DECLARE userSessions CURSOR FAST_FORWARD
FOR
        --  find all the user sessions in the order they were input for each user
        SELECT  t.UserID
              , t.[Time]
              , t.EventType
        FROM    dbo.test AS t
        ORDER BY t.UserID
              , t.ID;

OPEN userSessions;
FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
SET @LastUserID = @UserID;


WHILE @@FETCH_STATUS = 0
      BEGIN 
            --  same user, determine if this is a login or logout and record it in the #results table
            IF @UserID = @LastUserID
               BEGIN
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
                     IF @EventType = 'logout'
                        BEGIN 
                              UPDATE    #results
                              SET       LogoutTime = @Time
                              WHERE     ID = @CurrentSessionRowID;
                        END;

               END;
            ELSE
               BEGIN
                     -- new user, make sure the first record is login and start recording sessions
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
               END;



            SET @LastUserID = @UserID;
            FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
      END;
DEALLOCATE userSessions;

--  Query the results however you want with grouping and aggregates
SELECT  *
FROM    #results AS r;

DROP TABLE #results;
Related Question