0

If we have a table in SQL Server with the following data:

ID      Log_Time
1110    2016-10-31 20:34:50.000
1110    2016-10-31 20:34:58.000
1110    2016-10-31 20:35:03.000
1110    2016-11-01 01:28:29.000
1110    2016-11-01 01:28:33.000
1110    2016-11-01 01:28:37.000
1110    2016-11-01 01:28:42.000
1110    2016-11-01 01:28:46.000
1110    2016-11-01 01:28:50.000
1110    2016-11-01 01:28:54.000
1110    2016-11-01 01:28:59.000
1110    2016-11-01 01:29:03.000

Let's say each user action generates a log_time entry. The business calculates the number of sessions for billing purposes like this - start with session 1 and for each log activity, if the time difference is more than an hour, increment the session count by 1.

This is a fairly large table with different user ids. I have tried a combination of cursors for looping through distinct users and WHILE LOOPS to increment by doing the session counts iterating row by row. It takes long time to complete and when this table grows bigger, this might not even be the right approach. There must be a better way to do this. Any pointers?

The resultset I need is this:

ID     SessionCount
1110   28
1145   42
1116   38
  • 1
    It's not clear exactly how your counting works. Do you only need to compare each row to the previous row? If so LAG() sounds like it would help. – Joe Obbish Nov 16 '16 at 22:03
2

As Joe suggested, you should use the LAG() function to compare values to the previous row. It could be implemented like this:

With LaggedLogTime As (
    Select ID, Log_Time,
        PreviousTime = Lag(Log_Time, 1) Over (Partition By ID Order By Log_Time)
      From #log)
Select ID, 
    SessionCount = Sum(iif(DateDiff(Minute, PreviousTime, Log_Time) > 60 Or PreviousTime Is Null, 1, 0))
  From LaggedLogTime
  Group By ID;
|improve this answer|||||
  • 1
    You could also add 1900-01-01 (or a similarly old timestamp) as the default value for LAG: LAG(Log_Time, 1, '19000101') and get rid of the Or PreviousTime Is Null in the IIF condition. – Andriy M Nov 18 '16 at 8:54

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.