1

I want to be able to calculate the total time between certain events in SQL Server 2008.

I have a table as follows:

UserId   Event      EventTimestamp
+-------+----------+---------------+
 1       CheckedIn  14-05-15 10:01
 2       CheckedIn  14-05-15 10:15
 3       CheckedIn  14-05-15 10:17
 1       CheckedOut 14-05-15 10:25
 1       Logout     14-05-15 10:26

If you take a look at the table, a user can check-in, then can check-out and then go offline.

I want to calculate the time between a person checking-in to the point they check-out grouped by each week where the duration (between check-in and check-out) is less than 10 minutes.

So far I've managed to group by week, but I can't seem to calculate the duration between those events (which will happen in that order)...

select      
   'Week ' + cast(datepart(wk, EventUpdateStamp) as varchar(2)) Week,     
    UserId,
    Event 
from MyTable 
group by datepart(wk, EventUpdateStamp), UserId, Event

I would like to have the results like this:

UserId   TotalSessionCheckInTimeMinutes  WeekNumber   
+-------+------------------------------+---------------+
 1       24                             43

Taking the first row as an example:

  • 1 is the User's ID
  • 24 is the total duration in minutes between his check-in and check-out
  • 43 is the week number in the year

I only want to include check-in to check-out. Don't want to measure between check out and logout (in other words, measure how long he was checked-in for).

AshesToAshes
  • 937
  • 3
  • 14
  • 31

2 Answers2

4

Try this:

SELECT t1.UserId, 
    DATEDIFF(minute,
        t1.EventTimestamp,
        (SELECT MIN(t3.EventTimestamp)
        FROM MyTable t3
        WHERE t3.UserId = t1.UserId
        AND t3.EventTimestamp> t1.EventTimestamp)
    ), 
   CAST(DATEPART(wk, EventTimestamp) as varchar(2)) Week
FROM MyTable t1
WHERE EXISTS(
    SELECT 'NEXT'
    FROM MyTable t2
   WHERE t2.UserId = t1.UserId
   AND t2.EventTimestamp> t1.EventTimestamp
   AND t2.Event != 'Logout')

SqlFiddle

Go here

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
1

You can try this:

SELECT DISTINCT M.UserId, DATEPART(HOUR,A.TotalSessionCheckInTimeMinutes)*60 + DATEPART(MINUTE,A.TotalSessionCheckInTimeMinutes) AS TotalSessionCheckInTimeMinutes, DATEPART(WEEKDAY, M.EventTimestamp) FROM Mytable M
     CROSS APPLY (
        SELECT CAST((M1.EventTimestamp - M2.EventTimestamp) AS TIME) AS TotalSessionCheckInTimeMinutes
        FROM Mytable M1
          CROSS JOIN Mytable M2
            WHERE M1.Event = 'CheckOut' AND M2.Event = 'CheckIn' AND M1.UserId = M.UserId) AS A
UNION ALL
SELECT DISTINCT M.UserId, A.TotalSessionCheckInTimeMinutes, DATEPART(WEEKDAY, M.EventTimestamp) FROM Mytable M
     CROSS APPLY (
       SELECT CAST((M1.EventTimestamp - M2.EventTimestamp) AS TIME) AS TotalSessionCheckInTimeMinutes
       FROM Mytable M1
          CROSS JOIN Mytable M2
            WHERE M1.Event = 'LogOut' AND M2.Event = 'CheckIn' AND M1.UserId = M.UserId) AS A
Nguyễn Hải Triều
  • 1,454
  • 1
  • 8
  • 14
  • Doesn't seem to give the right data as it gives me a date in TotalSessionCheckInTimeMinutes in the year 1899 and the wrong week number. – AshesToAshes Oct 21 '15 at 08:59
  • I think problems in my code here: `M1.EventTimestamp - M2.EventTimestamp`. You can use convert or cast. :) – Nguyễn Hải Triều Oct 21 '15 at 09:01
  • Thanks - I think you are close but its saying 'Operand type clash: int is incompatible with time' – AshesToAshes Oct 21 '15 at 09:09
  • Here's the schema: CREATE TABLE [dbo].[MyTable]( [UserId] [int] NOT NULL, [Event] [varchar](30) NOT NULL, [EventTimestamp] [datetime] NOT NULL ) ON [PRIMARY] – AshesToAshes Oct 21 '15 at 09:09
  • You can reference answers in [this link](http://stackoverflow.com/questions/7176596/operand-type-clash-int-is-incompatible-with-date-the-insert-statement-conflicte) – Nguyễn Hải Triều Oct 21 '15 at 09:15