Solved

# How To Calculate vehicle Stop and Vehicle Running Time By SQL Query

Posted on 2013-09-22
Medium Priority
651 Views
Hi Expert,
We have GPS device date in sql server we need to calculate vehicle stop and running time ,
there is a field "speed"  to identify the vehicle movement , If vehicle is moving there is speed if not moving there is no speed.
So now we need to calculate the total stop time and total running time.

For your understanding I am sending a snap to let you check the data.
Data.png
0
Question by:Asrar Azmi
• 4
• 2
• 2
• +3

LVL 72

Expert Comment

ID: 39512867
Since the values are written once a minute, you just need to count of entries having no speed versus such having speed values, to get the number of minutes for each.
``````select devicenum,
sum(case speed when 0 then 1 else 0 end) stoptime,
sum(case speed when 0 then 0 else 1 end) runtime,
count(*) entries
from gphistory
where systime >= '2013-9-22 00:00' and systime < '2023-09-23'
group by devicenum
``````
0

LVL 50

Expert Comment

ID: 39512875
The first question I would ask is: when do the stop and running times begin and end?

Do you only want to compute the difference between the [SysTime] field where [Speed] = 0 to get the total stopped time, or do you want to compute the time as the total between the last time showing movement and the next time showing movement.

What about if in three consecutive records you are moving, stopped, then moving?  This would probably imply a stop sign or light.
0

LVL 82

Expert Comment

ID: 39512910
Depending on your device, the current configuration is to transmit an event on a minute by minute basis. To fyed's point, to have an accurate representation of running and stopped Time, is to exclude stop at a light, stop sign, as counting towards stop time.
This is done by reporting more frequently when the vehicle I moving while having a less frequent reporting period when the vehicle is stationary.
This is because of a timed reporting you can be stopped at a light when the reporting event is triggered.
In a scenario such as above, I would calculate only the stop times where there are consecutive events where speed is zero*minute separation. Diff between min(systime) and max(systime) - stopped time,
0

Author Comment

ID: 39512947
Dear Expert,

I need to count total stop ans total running time by systime date diffrence not by no of rows may be by min(systime) ans max(systime).
0

LVL 72

Expert Comment

ID: 39513009
That still won't work, as described above. You'll need to define a trigger limit for "stop" time for proper evaluation. Otherwise, counting the rows is exactly the same as calculating "systime - priorSystime" ...
0

LVL 5

Expert Comment

ID: 39514028
Nice problem, as you'll need to compare two records for starting or stopping.

I did solve this in the past by using an additional autonumber that's incremented by 1 and joined the table to itself by using:  Autonum = Autonum - 1

Thus you get two records "concatenated" and you're able to compare rec 1's speed is 0 and rec 2's speed is > 0 to get a start and the opposite for a stop.

So in this query you test for all records WHERE (speed1 0 and speed2 > 0) or  (speed1 > 0 and speed2 = 0). Thus you'll get for each start / stop one row and adding a column like:

``````CASE WHEN SPEED1 = 0 THEN 'Start' ELSE 'Stop' END as StartStop
``````

0

LVL 49

Expert Comment

ID: 39514053

To demonstrate Qlemo's point, here is a result:
``````    | STOP_SUM_DIFF | SPEED_SUM_DIFF | STOP_COUNT | SPEED_COUNT |
|---------------|----------------|------------|-------------|
|             9 |             17 |          9 |          17 |
``````
produced from:
``````;WITH
CTE AS (
SELECT
*
, row_number() over (ORDER BY systime DESC) AS rn
FROM gpshistory
)
SELECT
sum(CASE WHEN cte.speed = 0 THEN isnull(datediff(MINUTE,c2.systime, cte.systime),0) ELSE 0 END) AS stop_sum_diff
, sum(CASE WHEN cte.speed > 0 THEN isnull(datediff(MINUTE,c2.systime, cte.systime),1) ELSE 0 END) AS speed_sum_diff
, count(CASE WHEN cte.speed = 0 THEN 1 ELSE NULL END) AS stop_count
, count(CASE WHEN cte.speed > 0 THEN 1 ELSE NULL END) AS speed_count
FROM CTE
LEFT JOIN CTE AS c2 ON cte.rn = c2.rn - 1
``````
using this approximated data (Excel generated)
``````CREATE TABLE gpshistory
([id] int, [eventcode] int, [Speed] decimal(5,2), [systime] datetime)
;

INSERT INTO gpshistory
([id], [eventcode], [Speed], [systime])
VALUES
(63101678, 35, 50, '2013-09-22 13:46:19'),
(63101667, 35, 0, '2013-09-22 13:45:19'),
(63101656, 35, 0, '2013-09-22 13:44:19'),
(63101645, 35, 0, '2013-09-22 13:43:19'),
(63101634, 35, 0, '2013-09-22 13:42:19'),
(63101623, 35, 63, '2013-09-22 13:41:19'),
(63101612, 35, 89, '2013-09-22 13:40:19'),
(63101601, 35, 23, '2013-09-22 13:39:19'),
(63101590, 35, 68, '2013-09-22 13:38:19'),
(63101579, 35, 68, '2013-09-22 13:37:19'),
(63101568, 35, 57, '2013-09-22 13:36:19'),
(63101557, 35, 78, '2013-09-22 13:35:19'),
(63101546, 35, 81, '2013-09-22 13:34:19'),
(63101535, 35, 0, '2013-09-22 13:33:19'),
(63101524, 35, 75, '2013-09-22 13:32:19'),
(63101513, 35, 68, '2013-09-22 13:31:19'),
(63101502, 35, 0, '2013-09-22 13:30:19'),
(63101491, 35, 68, '2013-09-22 13:29:19'),
(63101480, 35, 87, '2013-09-22 13:28:19'),
(63101469, 35, 43, '2013-09-22 13:27:19'),
(63101458, 35, 0, '2013-09-22 13:26:19'),
(63101447, 35, 0, '2013-09-22 13:25:19'),
(63101436, 35, 64, '2013-09-22 13:24:19'),
(63101425, 35, 30, '2013-09-22 13:23:19'),
(63101414, 35, 0, '2013-09-22 13:22:19'),
(63101403, 35, 30, '2013-09-22 13:21:19')
;
``````
You need different logic than just deducting the systime by a single record offset to arrive at a different result
0

LVL 49

Accepted Solution

PortletPaul earned 1500 total points
ID: 39514078
'something more' might be to only recognize 2 or more consecutive records of zero as "stop", e.g.
``````    | STOP_SUM_DIFF | SPEED_SUM_DIFF | TOT_RECS | STOP_COUNT | SPEED_COUNT |
|---------------|----------------|----------|------------|-------------|
|             4 |             22 |       26 |          4 |          22 |
``````
``````;WITH
CTE AS (
SELECT
*
, row_number() over (ORDER BY systime DESC) AS rn
FROM gpshistory
)
SELECT
sum(CASE WHEN cte.speed = 0 and c2.speed = 0 THEN isnull(datediff(MINUTE,c2.systime, cte.systime),0) ELSE 0 END) AS stop_sum_diff
, sum(CASE WHEN cte.speed > 0 or c2.speed > 0 THEN isnull(datediff(MINUTE,c2.systime, cte.systime),1) ELSE 0 END) AS speed_sum_diff
, count(*) tot_recs
, count(CASE WHEN cte.speed = 0 and c2.speed = 0 THEN 1 ELSE NULL END) AS stop_count
, count(CASE WHEN cte.speed > 0 or c2.speed > 0 THEN 1 ELSE NULL END) AS speed_count
FROM CTE
LEFT JOIN CTE AS c2 ON cte.rn = c2.rn - 1
;
``````
By the way, if you are using SQL 2012 this query could be simplified by using LEAD/LAG functions (are you using sql 2012?).
0

LVL 5

Expert Comment

ID: 39514287
Guess you proved my comment iso Qlemo's :-)

Btw great coding PortletPaul by adding the WITH for the autonum and the sum/datediff for the time calculation.
And thanks for pointing me to the LEAD/LAG !
0

LVL 49

Expert Comment

ID: 39514297
@ggzfab - apologies, I skipped over your comment - I was concentrating on the data issues that Qlemo, fyed & arnold discussed.

row_number() in CTE = v.useful!

& LEAD/LAG - I'm so used to having them in Oracle I feel pain when they are denied to me.
0

LVL 49

Expert Comment

ID: 39531794
http:#a39514078 should not be the accepted answer, it was chatter.

0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.