SQL calculates the time difference between two strings that only contain 2 specific values
I have a table that contains status updates about users on a mobile device; every time they change status or update their position, a new line 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'm trying to do is figure out how much time is left for each user in the Suspended state ... so there is a time difference between the suspended value and the Asset value in [new_value]. for each user.
I thought I could use LEAD () to find the next row for each user, but "Active", which marks the end of their paused time, is rarely the next row ... it could be any number of rows below the table.
So far my request 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 correct, it is the next row in the table for this user, not the next row corresponding to the corresponding "Active" value, which will denote the actual end of their paused duration, so any help in determining this would be much appreciated !
I am using MS SQL Server 2012.
source to share
Assuming the only possible state change is 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'
source to share
You can use pivot and get this result, assuming you only have one change from active to pause and pause to active, as shown below:
select view_id, [user], [1] as [PauseStart], [2] as [PauseEnd], IdleTime = Datediff(MINUTE, [1],[2]) from (
select view_id, time_stamp, [user], RowN = Row_Number() over (partition by [user] order by time_stamp) from #youruser
where issue_event = 'state changed '
) a
pivot (max(time_stamp) for RowN in ([1],[2])) p
Output:
+---------+---------------+-------------------------+-------------------------+----------+
| view_id | user | PauseStart | PauseEnd | IdleTime |
+---------+---------------+-------------------------+-------------------------+----------+
| 6040462 | DerekRoberts | 2017-03-20 02:32:29.000 | 2017-03-20 03:01:03.000 | 29 |
| 6040461 | JamesMorrison | 2017-03-20 02:04:01.000 | 2017-03-20 02:34:11.000 | 30 |
+---------+---------------+-------------------------+-------------------------+----------+
source to share
Another possibility is an EXTERNAL APPLICATION with an additional request (example below). Change OUTER APPLY to CROSS APPLY if you don't want to catch pauses that haven't ended yet.
CREATE TABLE #Table (
view_id int,
time_stamp DateTime,
[user] nvarchar(50),
issue_event nvarchar(50),
original_value nvarchar(30),
new_value nvarchar(40))
INSERT INTO #Table
VALUES
(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')
,(6040462,'2017-03-21 03:32:29','DerekRoberts','state changed','Active','Paused')
,(6040462,'2017-03-21 04:05:03','DerekRoberts','state changed','Paused','Active')
,(6040461,'2017-03-22 02:04:01','JamesMorrison','state changed','Active','Paused')
SELECT
*
FROM
#Table
SELECT
[user],
CONVERT(nvarchar(10), pausedEvent.time_stamp, 102) ShiftDate,
pausedEvent.time_stamp PauseStart,
activatedEvent.time_stamp PauseEnd,
DATEDIFF(minute, pausedEvent.time_stamp, activatedEvent.time_stamp) IdleTime
FROM
#Table pausedEvent
OUTER APPLY (
SELECT TOP 1
activatedEvent.time_stamp
FROM
#Table activatedEvent
WHERE
activatedEvent.issue_event = 'state changed'
and activatedEvent.original_value = 'Paused'
and activatedEvent.new_value = 'Active'
and activatedEvent.time_stamp > pausedEvent.time_stamp
and activatedEvent.[user] = pausedEvent.[user]
ORDER BY time_stamp
) activatedEvent
WHERE
pausedEvent.issue_event = 'state changed'
and pausedEvent.original_value = 'Active'
and pausedEvent.new_value = 'Paused'
DROP TABLE #Table
Note that the performance of the various parameters may vary depending on the available indexes (I'm not sure how fast that would be).
source to share