MS SQL GROUPED SUM

Question

I currently have an MS SQL query which calculates the length of time that each User has been logged in to a system during one day. The table I am extracting this information from records each log in/log out as a separate record. Currently, my MS SQL code is as follows:

SELECT 
    CAST(DateTime As Date), 
    UserID, 
    MIN(DateTime),
    MAX(DateTime),
    DATEDIFF(SS, MIN(DateTime), MAX(DateTime))
FROM 
    LoginLogoutData 
WHERE 
    CAST(DateTime AS DATE) = '01/01/2015' 
GROUP BY 
    CAST(DateTime As Date), 
    UserID

This works as required and creates a table similar to the below.

    Date        UserID  FirstLogIn   FinalLogOut    LoggedInTime
  .........     ......  ..........   ............   ............
  01/01/2015    ABC     07:42:57     14:57:13         26056    
  01/01/2015    DEF     07:45:49     13:57:56         22326  

This works fine for one day's-worth of data. However, if I wanted to calculate the length of time that someone was logged into the system for during a larger date range, e.g. a week or month, this would not work; it would calculate the length of time between the user's log in on the first day and their log out on the final day.

Basically, I would like my code to calculate (Max(DateTime) - Min(DateTime)) FOR EACH DAY then sum all these values together into one simple table grouped only by UserId. I would then be able to set my date range as I please and receive the correct results.

So I would have a table as follows:

  UserId     LoggedInTime
 ........   ............. 
    ABC         563287
    DEF         485823
    GEH         126789

I assume I need to use a GROUP BY within the MIN() function but I don't have much experience with this yet.

Does anyone have any experience with this? Any help would be greatly appreciated.

Thank you.

Answer 1

Since you are already calculating LoggedInTime for each date, the following query would be necessary

SELECT USERID,SUM(LoggedInTime) LoggedInTime
FROM YOURTABLE
GROUP BY USERID

UPDATE

Since you have one record for login and the next record for logout(irrespective of date),we can use the concept of SELF JOIN(a table that joins itself) to get logout datetime for corresponding login time.

DECLARE @FROMDATE DATETIME='2014-01-01 07:42:57.000'
DECLARE @TODATE DATETIME='2015-02-01 07:42:57.000'    

;WITH CTE AS
(    
    -- ROW_NUMBER() is taken as logic for self joining. 
    SELECT ROW_NUMBER() OVER(ORDER BY USERID,[DATETIME]) RNO,* 
    FROM #TEMP
    WHERE [DATETIME] >= @FROMDATE AND [DATETIME] < @TODATE 
)
,CTE2 AS
(
    -- Since we are using SELF JOIN,we will get the next row's 
    -- datetime(ie, Logout time for corresponding login time)
    SELECT C1.*,C2.[DATETIME] [DATETIME2],
    DATEDIFF(SS, C1.[DateTime], C2.[DateTime]) SECONDSDIFF 
    FROM CTE C1
    LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1
    WHERE C1.RNO%2 <> 0
    -- Since we get the next row's datetime in current row,
    -- we omit each logout time's row
)
SELECT USERID,SUM(SECONDSDIFF) SECONDSDIFF
FROM CTE2

• SQL FIDDLE

Answer 2

Here is an example of how to do this with working sample data and the T-SQL included.

 -- original table you described
 CREATE TABLE LoginLogoutData  (UserID int, DateTime DateTime)

 GO

 -- clean any previous sample records
 TRUNCATE TABLE LoginLogOutData

 /*local variables for iteration*/
 DECLARE @i int = 1
 DECLARE @n int
 DECLARE @entryDate DateTime = GETDATE()

 --populate the table with some sample data
 /* for each of the  five sample users, generate sample login and logout 
  data for 30 days. Each login and logout are simply an hour apart for demo purposes. */
 SET NOCOUNT ON

 -- iterate over 5 users (userid)
 WHILE (@i <= 5)
 BEGIN

 --set the initial counter for the date loop 
 SET @n = 1

--dated entry loop
WHILE (@n <= 30)
BEGIN
   -- increment to the next day
   SET @entryDate = DateAdd(dd,@n,GETDATE())

        --logged in entry
        INSERT INTO LoginLogoutData (DateTime, UserID)          
        SELECT @entryDate,@i 

        -- logged out entry
        INSERT INTO LoginLogoutData (DateTime, UserID)
        SELECT DateAdd(hh,1,@entryDate),@i 

    --increment counter 
    SET @n = @n+1
 END

 --increment counter 
 SET @i=@i+1
END

GO


/* demonstrate that for each user each day has entries and that
the code calculates (Max(DateTime) - Min(DateTime)) FOR EACH DAY 
*/
SELECT UserID, 
   MIN(DateTime) AS LoggedIn, 
   MAX(DateTime) AS LoggedOut, 
   DATEDIFF(SS, MIN(DateTime), MAX(DateTime)) AS LoginTime
FROM LoginLogoutData 
GROUP BY CAST(DateTime As Date), UserID

/*this is a table variable used to support the "sum all these values together into one 
simple table grouped only by UserId*/
DECLARE @SummedUserActivity AS TABLE (UserID int, DailyActivity int)



 -- group the subtotals from each day per user 
   INSERT INTO @SummedUserActivity (UserID, DailyActivity)
   SELECT UserID, DATEDIFF(SS, MIN(DateTime), MAX(DateTime))
   FROM LoginLogoutData 
   GROUP BY CAST(DateTime As Date), UserID

   -- demonstrate the sum of the subtotals grouped by userid
   SELECT UserID, SUM(DailyActivity) AS TotalActivity
   FROM @SummedUserActivity
   GROUP BY UserID

Answer 3

First you need to aggregate by date, and then by larger units of time. For instance, for the year to date:

SELECT UserId, SUM(diffsecs)
FROM (SELECT CAST(DateTime As Date) as thedate, UserID,
             DATEDIFF(second, MIN(DateTime), MAX(DateTime)) as diffsecs
      FROM LoginLogoutData
      GROUP BY CAST(DateTime As Date), UserID
     ) ud
WHERE thedate between '2015-01-01' and getdate();

Answer 4

You can use another group by statement to the first query like below:

Select UserID,SUM(LoggedTime)
FROM
(
SELECT CAST(DateTime As Date), 
       UserID, MIN(DateTime), 
       MAX(DateTime), 
       DATEDIFF(SS, MIN(DateTime), MAX(DateTime)) AS LoggedTime
FROM LoginLogoutData 
WHERE CAST(DateTime AS DATE) = '01/01/2015'
GROUP BY CAST(DateTime As Date), UserID
) As temp
GROUP BY UserID

Here you can change the where clause to match the data range. It will first calculate logged time for each day and then get the sum of all days per user.