SQL lag between specific events

Question

I am looking for finding a solution to get the lag between two specific events from a table in MSSQL.

The sample data is below.

The result Im looking to obtain is get the duration of a session which is become active from sleep (marked in the same colour). I dont want to include the duration of a session whose stage is changed to active from any other.

Update : Added the sample data with desired output. I dont needs to consider the duration between a session activity which went to active from wait, only sleep to active needs to be calcualted.

create table TestLag (id int, session_id int, activity_time datetime, activity_name nvarchar(10))

INSERT into TestLag Values (1,1,'2021-04-11 10:10:23','active');
INSERT into TestLag Values (2,1,'2021-04-12 10:12:30','sleep');
INSERT into TestLag Values (3,1,'2021-04-13 10:14:11','active');
INSERT into TestLag Values (4,2,'2021-04-14 10:21:21','sleep');
INSERT into TestLag Values (5,3,'2021-04-15 10:25:18','active');
INSERT into TestLag Values (6,2,'2021-04-16 10:25:18','active');
INSERT into TestLag Values (7,2,'2021-04-17 10:31:23','wait');
INSERT into TestLag Values (8,3,'2021-04-18 10:32:10','sleep');
INSERT into TestLag Values (9,1,'2021-04-19 10:35:28','wait');
INSERT into TestLag Values (10,3,'2021-04-20 10:37:50','active');
INSERT into TestLag Values (11,2,'2021-04-20 10:37:55','active');

Desired output 

session_id  activity_time           duration_session_in_sleep_before_active
1           2021-04-12 10:12:30     101
2           2021-04-14 10:21:21     237
3           2021-04-18 10:32:10     340

Thank you,

Peter

user_14868997 · Answer

You can do this with two LAG functions, one to get the previous time and one to get the previous activity

There is no need to cross-join anything

SELECT
    t.session_id,
    t.prev_time activity_time,
    datediff(second, prev_time, activity_time) duration_session_in_sleep_before_active
FROM (
    SELECT *,
        LAG(activity_time) OVER (PARTITION BY session_id ORDER BY activity_time) prev_time,
        LAG(activity_name) OVER (PARTITION BY session_id ORDER BY activity_time) prev_name
    FROM TestLag
) t
WHERE t.activity_name = 'active'
  AND t.prev_name = 'sleep'

user_12486643 · Answer

Try this:

  SELECT T1.session_id,
         T1.activity_time,
         DATEDIFF(second, T1.activity_time, T2.activity_time) AS SleepToActiveDurationInSeconds 
  FROM T T1
  CROSS APPLY
  (
    SELECT TOP 1 activity_time, activity_name 
    FROM T 
    WHERE session_id = T1.session_id AND activity_time > T1.activity_time  
    ORDER BY activity_time
  ) T2
  WHERE T1.activity_name = 'sleep' AND T2.activity_name = 'active'

Sample T:

id  session_id  activity_time        activity_name
1   1           2021-04-11 10:10:23  active
2   1           2021-04-12 10:12:30  sleep
3   1           2021-04-13 10:14:11  active
4   2           2021-04-14 10:21:21  sleep
5   3           2021-04-15 10:25:18  active
6   2           2021-04-16 10:25:18  active
7   2           2021-04-17 10:31:23  wait
8   3           2021-04-18 10:32:10  sleep
9   1           2021-04-19 10:35:28  wait
10  3           2021-04-20 10:37:50  active
11  2           2021-04-20 10:37:55  active

Output:

session_id  activity_time       SleepToActiveDurationInSeconds
1           2021-04-12 10:12:30 86501
2           2021-04-14 10:21:21 173037
3           2021-04-18 10:32:10 173140

user_7132550 · Answer

Try this (all comments included) at SQL fiddle:

-- Outer query needs to be grouped by, because JOIN will
-- result in sleep record to join any active record that happened 
-- after that sleep record - we want only the closest one,
-- thus min(activeTime)
select 
    session_id, 
    sleepTime, 
    min(activeTime), 
    datediff(second, sleepTime, min(activeTime)) sleepDuration 
from (
    select 
        tl1.session_id, 
        tl1.activity_time sleepTime, 
        tl2.activity_time activeTime
    from TestLag tl1
    join TestLag tl2 on
    -- here we join based on: the same session id
    -- sleep ID must be lower than active, so 
    -- we do not join cases when sleep occured before active,
    -- and filter only desired statuses.
    tl1.activity_name = 'sleep' and
    tl2.activity_name = 'active' and
    tl1.id < tl2.id and
    tl1.session_id = tl2.session_id
) tbl
group by session_id, sleepTime

user_5567509 · Answer

Better to use LEAD function to generate the output. Please let me know i further enhancements and explanations required.

 SELECT     
  [A].[session_id] AS [session_id]
  ,[A].[current_activity_time] AS [activity_time]
  ,DATEDIFF(MINUTE,[A].[current_activity_time], [A].[next_activity_time]) AS 
  [duration_session_in_sleep_before_active]
 FROM 
(

  SELECT     
   [T].[session_id]
  ,[T].[activity_time] AS  [current_activity_time]
  ,[T].[activity_name] AS  [current_activity_name]
  ,LEAD([T].[activity_time]) OVER (PARTITION BY [T].[session_id] ORDER BY  [T].[activity_time] ASC) AS [next_activity_time]
  ,LEAD(activity_name) over (PARTITION BY  [T].[session_id] ORDER BY  [T].[activity_time] ASC) AS [next_activity_name]
 FROM  [TestLag] AS [T]

) AS A
WHERE [A].[current_activity_name] ='sleep' 
  AND [A].[next_activity_name] = 'active'

Output:

SQL lag between specific events

4 Answers