January 15, 2015 at 12:33 pm
Hello,
I have an iterative query that I need to optmize with hopefully a set based approach. Every hour it looks at a vehicles gps data and loops over the data points looking for idle times longer than X minutes. If the vehicle has been idle, but less than X minutes and it starts moving, the idle time is reset, and begins checking for the threshold again when the speed <= 1.
For each vehicle, I only want to know the consecutive idle times greater than X seconds. the thing to note as well, if the idle time doesn't reach the threshold and the vehicle starts moving, the time is then considered moving time and not idle time.
here is the query - Thanks in advance for any help!
WHILE @iLoop = 1
BEGIN
If( @iCurRow is null) select @iCurRow = 1
Else select @iCurRow = @iCurRow + 1
IF @iCurRow is null Break
select @curTS = TS,
@curIgn = IgnitionOn,
@curSpeed = Speed,
@span = 0
from @temp where ID = @iCurRow
select @nextTS = TS,
@nextSpeed = Speed
from @temp where ID = @iCurRow+1
set @span= isnull(sum(datediff(second,@curTS, @nextTS)),0)
if(@curSpeed <= 1 and @curIgn = 1)
Begin
if(@isIdling = 1)
Begin
set @adjIdle = @adjIdle + @span
End
else if((@cumIdle + @span) >= @idleThreshold)
Begin
set @adjIdle = @adjIdle + @cumIdle + @span
set @cumIdle = 0
set @isIdling = 1
End
else
Begin
set @isIdling = 0
set @cumIdle = @cumIdle + @span
End
End
else if(@curSpeed > 1 and @curIgn = 1)
Begin
set @isIdling = 0
set @adjMove = @adjMove + @cumIdle + @span
set @cumIdle = 0
End
else if @curIgn = 1
Begin
set @isIdling = 0
set @cumIdle = @cumIdle + @span
End
else if @curIgn = 0
Begin
if((@cumIdle) >= @idleThreshold)
Begin
set @adjIdle = @adjIdle + @cumIdle
set @cumIdle = 0
set @isIdling = 0
End
else
Begin
set @adjMove = @adjMove + @cumIdle
set @cumIdle = 0
set @isIdling = 0
End
End
IF @iCurRow+1 = @iMaxRow Break;
END
End
January 15, 2015 at 12:42 pm
The code helps some, but a table definition (create table script) for the table the data is in would help a lot. (and some sample data).
On to the question - if you're using SQL 2012 or later, you can use LAG to read from the previous row. (otherwise, you might be able to use ROW_NUMBER() to do the same thing, but it's a little more difficult to do).
January 15, 2015 at 3:07 pm
Here is an table and an example:
CREATE TABLE [dbo].[DeviceDataStage] (
[DeviceDataID] BIGINT NOT NULL,
[DeviceIdentifier] VARCHAR (25) NULL,
[Lat] DECIMAL (9, 6) NULL,
[Lng] DECIMAL (9, 6) NULL,
[Speed] DECIMAL (4, 1) NULL,
[DeviceTimestamp] DATETIME2 (7) NULL,
[IgnitionOn] BIT NULL,
CONSTRAINT [PK_DeviceDataStage] PRIMARY KEY CLUSTERED ([DeviceDataID] ASC)
);
Idle threshold 5min.
TimestampSpeed (MPH)IgnitionOnStateEvent
12:0051Moving 2min
12:0201Idle Accum 2min
12:040.51Idle Accum 4min
12:0601Idle 6min5min Idle trigger exceeded
12:080.251Idle 8min
12:1001Idle 10min
12:1221Moving 2min
12:1461Moving 4min
12:16101Moving 6min
12:1801Idle Accum 2min
12:2001Idle Accum 4min
12:22151Moving 12min
12:24201Moving 14min
12:26301Moving 16min
12:2801Idle Accum 2min
12:3001Idle Accum 4min
January 15, 2015 at 3:21 pm
ryanabr (1/15/2015)
Here is an table and an example:
CREATE TABLE [dbo].[DeviceDataStage] (
[DeviceDataID] BIGINT NOT NULL,
[DeviceIdentifier] VARCHAR (25) NULL,
[Lat] DECIMAL (9, 6) NULL,
[Lng] DECIMAL (9, 6) NULL,
[Speed] DECIMAL (4, 1) NULL,
[DeviceTimestamp] DATETIME2 (7) NULL,
[IgnitionOn] BIT NULL,
CONSTRAINT [PK_DeviceDataStage] PRIMARY KEY CLUSTERED ([DeviceDataID] ASC)
);
Idle threshold 5min.
TimestampSpeed (MPH)IgnitionOnStateEvent
12:0051Moving 2min
12:0201Idle Accum 2min
12:040.51Idle Accum 4min
12:0601Idle 6min5min Idle trigger exceeded
12:080.251Idle 8min
12:1001Idle 10min
12:1221Moving 2min
12:1461Moving 4min
12:16101Moving 6min
12:1801Idle Accum 2min
12:2001Idle Accum 4min
12:22151Moving 12min
12:24201Moving 14min
12:26301Moving 16min
12:2801Idle Accum 2min
12:3001Idle Accum 4min
It may just be me, but I don't see a correlation between the table definition and sample data provided.
January 17, 2015 at 9:34 pm
Here is a complete example:
declare @temp as table
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
)
insert @temp select '1/17/2015 12:00',5,1
insert @temp select '1/17/2015 12:02',0,1
insert @temp select '1/17/2015 12:04',.5,1
insert @temp select '1/17/2015 12:06',0,1
insert @temp select '1/17/2015 12:08',0.25, 1
insert @temp select '1/17/2015 12:10',0,1
insert @temp select '1/17/2015 12:12',2,1
insert @temp select '1/17/2015 12:14',6,1
insert @temp select '1/17/2015 12:16',10,1
insert @temp select '1/17/2015 12:18',0,1
insert @temp select '1/17/2015 12:20',0,1
insert @temp select '1/17/2015 12:22',15,1
insert @temp select '1/17/2015 12:24',20,1
insert @temp select '1/17/2015 12:26',30,1
insert @temp select '1/17/2015 12:28',0,1
insert @temp select '1/17/2015 12:30',0,1
declare @iLoop bigint= 1
declare @iCurRow bigint
declare @iMaxRow bigint
declare @NextTS datetime2
declare @curTS datetime2
declare @curIgn bit
declare @curSpeed decimal(4,1)
declare @NextSpeed decimal(4,1)
select @iMaxRow = max(ID) from @temp
declare @adjIdle bigint = 0
declare @cumIdle bigint = 0
declare @adjMove bigint = 0
declare @span bigint = 0
declare @isIdling bit = 0
declare @idleThreshold bigint = 600 --(5min)
if(@idleThreshold is not null)
Begin
WHILE @iLoop = 1
BEGIN
If( @iCurRow is null) select @iCurRow = 1
Else select @iCurRow = @iCurRow + 1
IF @iCurRow is null Break
select @curTS = TS,
@curIgn = IgnitionOn,
@curSpeed = Speed,
@span = 0
from @temp where ID = @iCurRow
select @nextTS = TS,
@nextSpeed = Speed
from @temp where ID = @iCurRow+1
set @span= isnull(sum(datediff(second,@curTS, @nextTS)),0)
print cast(@curTS as varchar) +
' is idle: ' + cast(@isIdling as varchar) +
' cum idle: ' + cast(@cumIdle as varchar) +
' adj idle: ' + cast(@adjIdle as varchar) +
' span: ' + cast(@span as varchar)
if(@curSpeed <= 1 and @curIgn = 1)
Begin
if(@isIdling = 1)
Begin
set @adjIdle = @adjIdle + @span
End
else if((@cumIdle + @span) >= @idleThreshold)
Begin
set @adjIdle = @adjIdle + @cumIdle + @span
set @cumIdle = 0
set @isIdling = 1
End
else
Begin
set @isIdling = 0
set @cumIdle = @cumIdle + @span
End
End
else if(@curSpeed > 1 and @curIgn = 1)
Begin
set @isIdling = 0
set @adjMove = @adjMove + @cumIdle + @span
set @cumIdle = 0
End
else if @curIgn = 1
Begin
set @isIdling = 0
set @cumIdle = @cumIdle + @span
End
else if @curIgn = 0
Begin
if((@cumIdle) >= @idleThreshold)
Begin
set @adjIdle = @adjIdle + @cumIdle
set @cumIdle = 0
set @isIdling = 0
End
else
Begin
set @adjMove = @adjMove + @cumIdle
set @cumIdle = 0
set @isIdling = 0
End
End
IF @iCurRow+1 = @iMaxRow Break;
END
End
set @adjMove = @adjMove + @cumIdle
select @adjIdle / 60
January 18, 2015 at 12:09 am
Your data is contradictory. A vehicle is moving whenever Speed>0, but your data says that it's not moving unless speed>1.
If you have SQL Server 2012 or later this can most likely be solved using windowing functions... so what version of SQL Server are you using?
I did this in 2012... I think it's close.
Here's a modified dataset, so that the gaps are obvious. Since the measurements are taken every 2 seconds, a vehicle stopped for 3 intervals or more (6 seconds) should meet your criteria. (I think.)
CREATE TABLE CarMotion
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
);
GO
insert CarMotion select '1/17/2015 12:00',5,1
insert CarMotion select '1/17/2015 12:02',0,1
insert CarMotion select '1/17/2015 12:04',0,1
insert CarMotion select '1/17/2015 12:06',0,1
insert CarMotion select '1/17/2015 12:08',0, 1
insert CarMotion select '1/17/2015 12:10',0,1
insert CarMotion select '1/17/2015 12:12',2,1
insert CarMotion select '1/17/2015 12:14',6,1
insert CarMotion select '1/17/2015 12:16',10,1
insert CarMotion select '1/17/2015 12:18',0,1
insert CarMotion select '1/17/2015 12:20',0,1
insert CarMotion select '1/17/2015 12:22',0,1
insert CarMotion select '1/17/2015 12:24',20,1
insert CarMotion select '1/17/2015 12:26',30,1
insert CarMotion select '1/17/2015 12:28',0,1
insert CarMotion select '1/17/2015 12:30',0,1
SELECT *
FROM
(
SELECT VehicleID
, TS
, nxt
, Speed
, CASE WHEN Speed>0 THEN 1 ELSE 0 END AS IsMoving
, ROW_NUMBER() OVER (PARTITION BY Speed ORDER BY TS) AS numSeq
FROM
(SELECT 1 AS VehicleID
, TS
, LEAD(TS,1) OVER (ORDER BY ts) AS nxt
, Speed
FROM CarMotion) x
) y
WHERE IsMoving = 0 AND numSeq>3;
January 18, 2015 at 10:40 pm
This is relatively easy to do on SQL Server 2012 and later, can you let us know which version you are on? Meanwhile here is a quick set based solution for SQL Server 2005/2008 which will certainly outperform the looping method.
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @temp as table
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
)
insert @temp select '1/17/2015 12:00',5,1
insert @temp select '1/17/2015 12:02',0,1
insert @temp select '1/17/2015 12:04',.5,1
insert @temp select '1/17/2015 12:06',0,1
insert @temp select '1/17/2015 12:08',0.25, 1
insert @temp select '1/17/2015 12:10',0,1
insert @temp select '1/17/2015 12:12',2,1
insert @temp select '1/17/2015 12:14',6,1
insert @temp select '1/17/2015 12:16',10,1
insert @temp select '1/17/2015 12:18',0,1
insert @temp select '1/17/2015 12:20',0,1
insert @temp select '1/17/2015 12:22',15,1
insert @temp select '1/17/2015 12:24',20,1
insert @temp select '1/17/2015 12:26',30,1
insert @temp select '1/17/2015 12:28',0,1
insert @temp select '1/17/2015 12:30',0,1
DECLARE @SPEED_THRESHOLD DECIMAL(4,1) = 0.5;
;WITH BASE_DATA AS
(
SELECT
T.ID
,ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
) AS T_RID
,T.TS
,T.Speed
,SIGN(CEILING(T.Speed - @SPEED_THRESHOLD)) AS IS_MOVING
,T.IgnitionOn
FROM @temp T
)
,GROUP_DATA AS
(
SELECT
BD.ID
,BD.IgnitionOn
,BD.Speed
,BD.IS_MOVING
,BD.T_RID
,CASE
WHEN ISNULL(BLAG.IS_MOVING,BD.IS_MOVING) = BD.IS_MOVINGTHEN 0
ELSE 1
END AS STATE_CHANGE
,((CASE
WHEN ISNULL(BLAG.IS_MOVING,BD.IS_MOVING) = BD.IS_MOVINGTHEN 0
ELSE 1
END) +(BD.T_RID - DENSE_RANK() OVER (ORDER BY CASE
WHEN ISNULL(BLAG.IS_MOVING,-1) <> BD.IS_MOVINGTHEN BD.T_RID + 1
ELSE BD.T_RID
END))) AS GROUP_KEY
,BD.TS
,DATEDIFF(MINUTE,ISNULL(BLAG.TS,BD.TS),BD.TS) AS DURATION
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BLAG
ON BD.T_RID = (BLAG.T_RID + 1)
)
,SUMMARISED_DATA AS
(
SELECT
GD.ID
,GD.IgnitionOn
,GD.Speed
,GD.IS_MOVING
,GD.T_RID
,GD.STATE_CHANGE
,GD.GROUP_KEY
,GD.TS
,GD.DURATION
,(SELECT
SUM(GX.DURATION)
FROM GROUP_DATA GX
WHERE GX.GROUP_KEY = GD.GROUP_KEY
AND GX.T_RID <= GD.T_RID
) AS RT_DURATION
FROM GROUP_DATA GD
)
SELECT
SD.TS AS [Timestamp]
,SD.Speed AS [Speed (MPH)]
,SD.IgnitionOn AS [IgnitionOn]
,CASE
WHEN SD.IS_MOVING = 1 THEN 'Moving ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION < 5 THEN 'Idle Accum ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 THEN 'Idle ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
END AS [State]
,CASE
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 AND (SD.RT_DURATION - SD.DURATION) < 5 THEN '5min Idle trigger exceeded'
ELSE ''
END AS [Event]
FROM SUMMARISED_DATA SD;
Results
Timestamp Speed (MPH) IgnitionOn State Event
--------------------------- ------------ ---------- ------------------ --------------------------
2015-01-17 12:00:00.0000000 5.0 1 Moving 0min
2015-01-17 12:02:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:04:00.0000000 0.5 1 Idle Accum 4min
2015-01-17 12:06:00.0000000 0.0 1 Idle 6min 5min Idle trigger exceeded
2015-01-17 12:08:00.0000000 0.3 1 Idle 8min
2015-01-17 12:10:00.0000000 0.0 1 Idle 10min
2015-01-17 12:12:00.0000000 2.0 1 Moving 2min
2015-01-17 12:14:00.0000000 6.0 1 Moving 4min
2015-01-17 12:16:00.0000000 10.0 1 Moving 6min
2015-01-17 12:18:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:20:00.0000000 0.0 1 Idle Accum 4min
2015-01-17 12:22:00.0000000 15.0 1 Moving 2min
2015-01-17 12:24:00.0000000 20.0 1 Moving 4min
2015-01-17 12:26:00.0000000 30.0 1 Moving 6min
2015-01-17 12:28:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:30:00.0000000 0.0 1 Idle Accum 4min
Edit: typo
January 19, 2015 at 2:15 am
For completion, here is a SQL Server 2012 and later version using the window functions, roughly 3x faster than the 2005/2008 method
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @temp as table
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
)
insert @temp select '1/17/2015 12:00',5,1
insert @temp select '1/17/2015 12:02',0,1
insert @temp select '1/17/2015 12:04',.5,1
insert @temp select '1/17/2015 12:06',0,1
insert @temp select '1/17/2015 12:08',0.25, 1
insert @temp select '1/17/2015 12:10',0,1
insert @temp select '1/17/2015 12:12',2,1
insert @temp select '1/17/2015 12:14',6,1
insert @temp select '1/17/2015 12:16',10,1
insert @temp select '1/17/2015 12:18',0,1
insert @temp select '1/17/2015 12:20',0,1
insert @temp select '1/17/2015 12:22',15,1
insert @temp select '1/17/2015 12:24',20,1
insert @temp select '1/17/2015 12:26',30,1
insert @temp select '1/17/2015 12:28',0,1
insert @temp select '1/17/2015 12:30',0,1
DECLARE @SPEED_THRESHOLD DECIMAL(4,1) = 0.5;
;WITH BASE_DATA AS
(
SELECT
T.ID
,ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
) AS T_RID
,T.TS
,DATEDIFF(MINUTE,LAG(T.TS,1,T.TS) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
),T.TS) AS DURATION
,T.Speed
,SIGN(CEILING(T.Speed - @SPEED_THRESHOLD)) AS IS_MOVING
,CASE
WHEN SIGN(CEILING(T.Speed - @SPEED_THRESHOLD))
<> SIGN(CEILING((LAG(T.Speed,1,T.Speed) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
)) - @SPEED_THRESHOLD)) THEN 1
ELSE 0
END AS STATE_CHANGED
,T.IgnitionOn
FROM @temp T
)
,GROUPED_DATA AS
(
SELECT
BD.ID
,BD.T_RID
,BD.TS
,BD.DURATION
,BD.Speed
,BD.IS_MOVING
,SUM(BD.STATE_CHANGED) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BD.T_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GROUP_KEY
,BD.IgnitionOn
FROM BASE_DATA BD
)
,SUMMARISED_DATA AS
(
SELECT
GD.ID
,GD.T_RID
,GD.TS
,GD.DURATION
,SUM(GD.DURATION) OVER
(
PARTITION BY GD.GROUP_KEY
ORDER BY GD.T_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS RT_DURATION
,GD.Speed
,GD.IS_MOVING
,GD.IgnitionOn
FROM GROUPED_DATA GD
)
SELECT
SD.TS AS [Timestamp]
,SD.Speed AS [Speed (MPH)]
,SD.IgnitionOn AS [IgnitionOn]
,CASE
WHEN SD.IS_MOVING = 1 THEN 'Moving ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION < 5 THEN 'Idle Accum ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 THEN 'Idle ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
END AS [State]
,CASE
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 AND (SD.RT_DURATION - SD.DURATION) < 5 THEN '5min Idle trigger exceeded'
ELSE ''
END AS [Event]
FROM SUMMARISED_DATA SD;
Results
Timestamp Speed (MPH) IgnitionOn State Event
--------------------------- ------------ ---------- ---------------- --------------------------
2015-01-17 12:00:00.0000000 5.0 1 Moving 0min
2015-01-17 12:02:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:04:00.0000000 0.5 1 Idle Accum 4min
2015-01-17 12:06:00.0000000 0.0 1 Idle 6min 5min Idle trigger exceeded
2015-01-17 12:08:00.0000000 0.3 1 Idle 8min
2015-01-17 12:10:00.0000000 0.0 1 Idle 10min
2015-01-17 12:12:00.0000000 2.0 1 Moving 2min
2015-01-17 12:14:00.0000000 6.0 1 Moving 4min
2015-01-17 12:16:00.0000000 10.0 1 Moving 6min
2015-01-17 12:18:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:20:00.0000000 0.0 1 Idle Accum 4min
2015-01-17 12:22:00.0000000 15.0 1 Moving 2min
2015-01-17 12:24:00.0000000 20.0 1 Moving 4min
2015-01-17 12:26:00.0000000 30.0 1 Moving 6min
2015-01-17 12:28:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:30:00.0000000 0.0 1 Idle Accum 4min
January 19, 2015 at 6:45 pm
First, thanks everyone for your help on this, it is really appreciated!
I am using SQL Azure and I it looks like the LEAD and LAG keywords are not supported. Is there a substitute that is supported in SQL Azure?
January 19, 2015 at 9:40 pm
ryanabr (1/19/2015)
First, thanks everyone for your help on this, it is really appreciated!I am using SQL Azure and I it looks like the LEAD and LAG keywords are not supported. Is there a substitute that is supported in SQL Azure?
You are very welcome.
On Azure, you will have to go with the 2005/2008 method which uses self-joins instead of the cross-row referencing window functions.
😎
January 20, 2015 at 12:37 pm
I have been working with the 2005/2008 version of the query and it is very close. There are essentially 4 states
Stopped (IgnitionOn = 0)
Moving (IgnitionOn = 1 and Speed > .5)
Idle (IgnitionOn = 1 and Speed <.5 AND duration > 5min)
IdleAccumulate (IgnitionOn = 1 and Speed <.5 AND duration < 5min)
for our purposes, I think the Stopped state can be treated the Same as the Moving State so I have updated your query to set IS_MOVING to 1 for both the Moving state and the Stopped state, since I only am looking for Idle Times above the 5min threshold
There is just one case I cannot resolve, and that is where the data in T_RID 4, speed goes to 5MPH for one update and then back down below the speed threshold, we should go to the Moving state for 2min then back to IdleAccumulate for 2min, but what is happening is it is moving to the Idle state for 6min.
I cannot quite unwind your awesome query enough to figure it out to that level, was hoping you could spot it quickly:
declare @temp as table
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
)
insert @temp select '1/17/2015 12:00',5,0
insert @temp select '1/17/2015 12:02',0,0
insert @temp select '1/17/2015 12:04',.5,1
insert @temp select '1/17/2015 12:06',5,1
insert @temp select '1/17/2015 12:08',.25, 1
insert @temp select '1/17/2015 12:10',0,1
insert @temp select '1/17/2015 12:12',2,1
insert @temp select '1/17/2015 12:14',6,1
insert @temp select '1/17/2015 12:16',10,1
insert @temp select '1/17/2015 12:18',0,1
insert @temp select '1/17/2015 12:20',0,1
insert @temp select '1/17/2015 12:22',15,1
insert @temp select '1/17/2015 12:24',20,1
insert @temp select '1/17/2015 12:26',30,1
insert @temp select '1/17/2015 12:28',0,1
insert @temp select '1/17/2015 12:30',0,1
DECLARE @SPEED_THRESHOLD DECIMAL(4,1) = 0.5;
;WITH BASE_DATA AS
(
SELECT
T.ID
,ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
) AS T_RID
,T.TS
,T.Speed
,case when IgnitionOn = 0 then 1
else SIGN(CEILING(T.Speed - @SPEED_THRESHOLD)) end AS IS_MOVING
,T.IgnitionOn
FROM @temp T
)
,GROUP_DATA AS
(
SELECT
BD.ID
,BD.IgnitionOn
,BD.Speed
,BD.IS_MOVING
,BD.T_RID
,CASE
WHEN ISNULL(BLAG.IS_MOVING,BD.IS_MOVING) = BD.IS_MOVINGTHEN 0
ELSE 1
END AS STATE_CHANGE
,((CASE
WHEN ISNULL(BLAG.IS_MOVING,BD.IS_MOVING) = BD.IS_MOVINGTHEN 0
ELSE 1
END) +(BD.T_RID - DENSE_RANK() OVER (ORDER BY CASE
WHEN ISNULL(BLAG.IS_MOVING,-1) <> BD.IS_MOVINGTHEN BD.T_RID + 1
ELSE BD.T_RID
END))) AS GROUP_KEY
,BD.TS
,DATEDIFF(MINUTE,ISNULL(BLAG.TS,BD.TS),BD.TS) AS DURATION
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BLAG
ON BD.T_RID = (BLAG.T_RID + 1)
)
,SUMMARISED_DATA AS
(
SELECT
GD.ID
,GD.IgnitionOn
,GD.Speed
,GD.IS_MOVING
,GD.T_RID
,GD.STATE_CHANGE
,GD.GROUP_KEY
,GD.TS
,GD.DURATION
,(SELECT
SUM(GX.DURATION)
FROM GROUP_DATA GX
WHERE GX.GROUP_KEY = GD.GROUP_KEY
AND GX.T_RID <= GD.T_RID
) AS RT_DURATION
FROM GROUP_DATA GD
)
SELECT
SD.TS AS [Timestamp]
,SD.Speed AS [Speed (MPH)]
,SD.IgnitionOn AS [IgnitionOn]
,CASE
WHEN SD.IS_MOVING = 1 THEN 'Moving ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION < 5 THEN 'Idle Accum ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 THEN 'Idle ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
END AS [State]
,CASE
WHEN SD.IS_MOVING = 0 AND SD.RT_DURATION >= 5 AND (SD.RT_DURATION - SD.DURATION) < 5 THEN '5min Idle trigger exceeded'
ELSE ''
END AS [Event]
FROM SUMMARISED_DATA SD;
January 20, 2015 at 1:33 pm
Slight changes to accommodate for the multiple "State"
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @temp as table
(
ID int identity,
TS datetime2,
Speed decimal(4,1),
IgnitionOn bit
)
insert @temp select '1/17/2015 12:00',5,0
insert @temp select '1/17/2015 12:02',0,0
insert @temp select '1/17/2015 12:04',.5,1
insert @temp select '1/17/2015 12:06',5,1
insert @temp select '1/17/2015 12:08',.25, 1
insert @temp select '1/17/2015 12:10',0,1
insert @temp select '1/17/2015 12:12',2,1
insert @temp select '1/17/2015 12:14',6,1
insert @temp select '1/17/2015 12:16',10,1
insert @temp select '1/17/2015 12:18',0,1
insert @temp select '1/17/2015 12:20',0,1
insert @temp select '1/17/2015 12:22',15,1
insert @temp select '1/17/2015 12:24',20,1
insert @temp select '1/17/2015 12:26',30,1
insert @temp select '1/17/2015 12:28',0,1
insert @temp select '1/17/2015 12:30',0,1
insert @temp select '1/17/2015 12:32',0,1
DECLARE @SPEED_THRESHOLD DECIMAL(4,1) = 0.5;
;WITH BASE_DATA AS
(
SELECT
T.ID
,ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.TS
) AS T_RID
,T.TS
,T.Speed
,CASE
WHEN T.IgnitionOn = 0 THEN 0
WHEN T.IgnitionOn = 1 AND T.Speed <= @SPEED_THRESHOLD THEN 1
WHEN T.IgnitionOn = 1 AND T.Speed > @SPEED_THRESHOLD THEN 2
END AS T_STATE
,T.IgnitionOn
FROM @temp T
)
,GROUP_IDENTIFIER AS
(
SELECT
BD.ID
,BD.IgnitionOn
,BD.Speed
,BD.T_STATE
,BD.T_RID
,CASE
WHEN ISNULL(BLAG.T_STATE,BD.T_STATE) = BD.T_STATETHEN 0
ELSE 1
END AS STATE_CHANGE
,BD.TS
,DATEDIFF(MINUTE,ISNULL(BLAG.TS,BD.TS),BD.TS) AS DURATION
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BLAG
ON BD.T_RID = (BLAG.T_RID + 1)
)
,GROUP_DATA AS
(
SELECT
GD.ID
,GD.IgnitionOn
,GD.Speed
,GD.T_STATE
,GD.T_RID
,GD.STATE_CHANGE
,GD.TS
,GD.DURATION
,(SELECT SUM(X.STATE_CHANGE)
FROM GROUP_IDENTIFIER X
WHERE X.T_RID <= GD.T_RID) AS GROUP_KEY
FROM GROUP_IDENTIFIER GD
)
,SUMMARISED_DATA AS
(
SELECT
GD.ID
,GD.IgnitionOn
,GD.Speed
,GD.T_STATE
,GD.T_RID
,GD.STATE_CHANGE
,GD.GROUP_KEY
,GD.TS
,GD.DURATION
,(SELECT
SUM(GX.DURATION)
FROM GROUP_DATA GX
WHERE GX.GROUP_KEY = GD.GROUP_KEY
AND GX.T_RID <= GD.T_RID
) AS RT_DURATION
FROM GROUP_DATA GD
)
SELECT
SD.TS AS [Timestamp]
,SD.Speed AS [Speed (MPH)]
,SD.IgnitionOn AS [IgnitionOn]
,CASE
WHEN SD.T_STATE = 0 THEN 'Stopped ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.T_STATE = 2 THEN 'Moving ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.T_STATE = 1 AND SD.RT_DURATION < 5 THEN 'Idle Accum ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
WHEN SD.T_STATE = 1 AND SD.RT_DURATION >= 5 THEN 'Idle ' + CONVERT(VARCHAR(12),SD.RT_DURATION,0) + 'min'
END AS [State]
,CASE
WHEN SD.T_STATE = 1 AND SD.RT_DURATION >= 5 AND (SD.RT_DURATION - SD.DURATION) < 5 THEN '5min Idle trigger exceeded'
ELSE ''
END AS [Event]
FROM SUMMARISED_DATA SD;
Results
Timestamp Speed (MPH) IgnitionOn State Event
--------------------------- ------------- ---------- -------------------------- --------------------------
2015-01-17 12:00:00.0000000 5.0 0 Stopped 0min
2015-01-17 12:02:00.0000000 0.0 0 Stopped 2min
2015-01-17 12:04:00.0000000 0.5 1 Idle Accum 2min
2015-01-17 12:06:00.0000000 5.0 1 Moving 2min
2015-01-17 12:08:00.0000000 0.3 1 Idle Accum 2min
2015-01-17 12:10:00.0000000 0.0 1 Idle Accum 4min
2015-01-17 12:12:00.0000000 2.0 1 Moving 2min
2015-01-17 12:14:00.0000000 6.0 1 Moving 4min
2015-01-17 12:16:00.0000000 10.0 1 Moving 6min
2015-01-17 12:18:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:20:00.0000000 0.0 1 Idle Accum 4min
2015-01-17 12:22:00.0000000 15.0 1 Moving 2min
2015-01-17 12:24:00.0000000 20.0 1 Moving 4min
2015-01-17 12:26:00.0000000 30.0 1 Moving 6min
2015-01-17 12:28:00.0000000 0.0 1 Idle Accum 2min
2015-01-17 12:30:00.0000000 0.0 1 Idle Accum 4min
2015-01-17 12:32:00.0000000 0.0 1 Idle 6min 5min Idle trigger exceeded
January 21, 2015 at 12:22 pm
You are the MAN! look me up if you are ever in Minneapolis, I owe you a beer (keg really...) !
Thanks again!
January 21, 2015 at 2:03 pm
ryanabr (1/21/2015)
You are the MAN! look me up if you are ever in Minneapolis, I owe you a beer (keg really...) !Thanks again!
No worries, glad to help.
One of my favourites is Goose Island Bourbon County Stout, one doesn't need a keg of that one though:-P
😎
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply