SQL calculate time difference between two rows that contain 2 specific values only

Question

I have a table that contains status updates about users on a mobile device; every time they change status or their position is updated, a new row is created:

view_id  time_stamp           user           issue_event       original_value   new_value
-----------------------------------------------------------------------------------------
6040462  2017-03-20 02:00:43  DerekRoberts   Position updated  NULL             NULL
6040461  2017-03-20 02:04:01  JamesMorrison  state changed     Active           Paused
6040461  2017-03-20 02:08:33  JamesMorrison  Position updated  NULL             NULL
6040462  2017-03-20 02:20:42  DerekRoberts   Position updated  NULL             NULL
6040462  2017-03-20 02:32:29  DerekRoberts   state changed     Active           Paused
6040461  2017-03-20 02:34:11  JamesMorrison  state changed     Paused           Active
6040461  2017-03-20 02:36:22  JamesMorrison  Position updated  NULL             NULL
6040462  2017-03-20 02:52:47  DerekRoberts   Position updated  NULL             NULL
6040462  2017-03-20 03:01:03  DerekRoberts   state changed     Paused           Active

What I am trying to do is find out the length of time each user remains in the 'Paused' state for... so that's the difference in time between the 'Paused' value and the 'Active' value in the [new_value] column. for each user.

I thought I could use LEAD() to find the next row for each user, but the 'Active' that marks the end of their paused time is rarely the next row... it could be any number of rows further down the table.

So far, my query looks like this:

;WITH UserPauseActivity AS
(SELECT [new_value],
 ShiftDate = CAST([time_stamp] as DATE),
 PauseStartUser = [user],
 PauseEndUser = LEAD([user], 1) OVER(ORDER BY [user], [time_stamp]),
 PauseStart = [time_stamp],
 PauseEnd = LEAD([time_stamp], 1) OVER(ORDER BY [user], [time_stamp]),
 PauseStartDate = CAST([time_stamp] AS DATE),
 PauseEndDate = CAST(LEAD([time_stamp], 1) OVER(ORDER BY [user], [time_stamp]) AS DATE)
FROM [SAFE].[dbo].[cc_shift_log_view])

SELECT PauseStartUser [user],
ShiftDate,
PauseStart,
PauseEnd,
DATEDIFF(minute, PauseStart, PauseEnd) IdleTime
FROM UserPauseActivity
WHERE [new_value] = 'Paused'
AND PauseEnd IS NOT NULL
AND PauseStartUser = PauseEndUser
AND PauseStartDate = PauseEndDate
AND PauseStartDate >= '2017-03-20 00:00:00' and PauseStartDate <= '2017-03-21 23:59:59'
ORDER BY ShiftDate, [user]

Which returns this:

user             ShiftDate    PauseStart           PauseEnd              IdleTime
---------------------------------------------------------------------------------
JamesMorrison    2017-03-20   2017-03-20 02:04:01  2017-03-20 02:08:33   4
DerekRoberts     2017-03-20   2017-03-20 02:32:29  2017-03-20 02:52:47   20

The PauseStart value is correct, but the PauseEnd is not right, being the next row in the table for that user, rather than the next row contining the corresponding 'Active' value which would mark the actual end of their paused duration, so any help in figuring this out would be much appreciated!

I'm using MS SQL Server 2012.

Answer 1

Under the assumption that the only state change possible is from Active -> Paused -> Active..., you can use lead to get the desired result.

select usr,shiftDate,pause_start,pause_end,datediff(second,pause_start,pause_end)/60.0 as idleTime
from (select usr,cast(time_stamp as date) as shiftDate,time_stamp as pause_start
      ,lead(time_stamp) over(partition by usr order by time_stamp) as pause_end
      ,original_value,new_value
      from t
      where issue_event='state changed'
     ) t 
where original_value='Active' and new_value='Paused'