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

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
Asrar AzmiWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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

Open in new window

0
Dale FyeCommented:
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
arnoldCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Asrar AzmiWeb DeveloperAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
ggzfabCommented:
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

Open in new window


will make it readable.
0
PortletPaulfreelancerCommented:
no points please.

To demonstrate Qlemo's point, here is a result:
    | STOP_SUM_DIFF | SPEED_SUM_DIFF | STOP_COUNT | SPEED_COUNT |
    |---------------|----------------|------------|-------------|
    |             9 |             17 |          9 |          17 |

Open in new window

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

Open in new window

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')
;

Open in new window

You need different logic than just deducting the systime by a single record offset to arrive at a different result
0
PortletPaulfreelancerCommented:
'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 |

Open in new window

;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
;

Open in new window

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ggzfabCommented:
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
PortletPaulfreelancerCommented:
@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
PortletPaulfreelancerCommented:
http:#a39514078 should not be the accepted answer, it was chatter.

Please also note I was offering a 'no points please' explanation of Qlemo's answer at http:#39512867
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.