2

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.

  • DATETIME and DATETIME2 are compatible data types in the application layer so you should be able to upgrade to DATETIME2. Plus you should be able to wrap data access into a stored proc which would let you accept a string and store an int for EventType. Also you should consider not using a data type keyword as your column name. The point of the edge cases I mentioned was to get you thinking about ways your script can fail. Under your current system if I get the blue screen of death at the end of my work day and leave without restarting the application it would show me as active all night – Erik Nov 17 '15 at 15:10
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.