Counting sessions - TSQL 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
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) &gt; 60 Or PreviousTime Is Null, 1, 0))
  From LaggedLogTime
  Group By ID;