SQL Server - Counting Sessions - Gaps and islands

Question

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 result set I need is this:

ID     SessionCount
1110   28
1145   42
1116   38

score 3 · Accepted Answer · answered Nov 16 '16 at 18:58

Maybe something like this:

select ID, sum(case when diff is NULL or diff > 60 then 1 else 0 end)
from (
  select
    ID,
    datediff(minute, lag(Log_Time) 
    over (partition by ID order by Log_Time), Log_time) as diff
  from
    #tmp
) X
group by ID

This will check the lag to previous row, if it's over 60 (or null, for the first row) then it's calculated as 1 into the sum, and otherwise as zero.

score 0 · Answer 2 · answered Nov 16 '16 at 19:09

You can do it using "LEAD" and "DATEDIFF". You find the next "LogTime" value by LEAD.

SELECT
    A.ID,
    COUNT(A.ID) AS SesionCount
FROM
(
    SELECT 
        T.ID,
        DATEDIFF(HOUR, T.Log_Time, LEAD(T.Log_Time) OVER (ORDER BY T.Log_Time)) AS LogDateDiff
    FROM 
        @Tbl T
) A
WHERE
    A.LogDateDiff >= 1
GROUP BY
    A.ID

SQL Server - Counting Sessions - Gaps and islands

2 Answers2

Linked