Solved

# SQL Query

Posted on 2014-11-03
84 Views
Hi, Expert
We have gps system each time device sending data every minute , if the vehicle is moving it has speed data if the vehicle is stopped then speed =0
I need to Calculate the total stopped time and location of the vehicle where is stopped and how long.

Please send me if you have any example

Columns as describe below

devicenum
latitude
longitude
speed
systime
0
Question by:Asrar Azmi
• 4
• 3

LVL 8

Expert Comment

ID: 40418930
Your query will be something like:
``````select count(*), latitude, longitude from YOUR_TABLE group by latitude, longitude where speed=0
``````
This will give you the locations of vehicle where is stopped and how long.
regarding the total stop time regardless of where may be as this:
``````select count(*) from YOUR_TABLE  where speed=0
``````
Put in mind the result is number of minutes as you said the data sending is every minute.
0

Author Comment

ID: 40418953
Dear Expert,

How we can display systime column also with particular location like Stop Duration Like From To date time.

0

LVL 24

Expert Comment

ID: 40418991

``````with T1 as (
select *, sum(case speed when 0 then 0 else 1 end) over(order by systime) as MyGroup
from YOUR_TABLE)
select devicenum, latitude, longitude, min(systime) as MinTime, max(systime) as MaxTime
from T1
group by devicenum, latitude, longitude, MyGroup
``````
0

LVL 8

Expert Comment

ID: 40419137
``````select count(*), latitude, longitude from YOUR_TABLE group by latitude, longitude, min(systime) From, max(systime) To where speed=0
``````
0

Author Comment

ID: 40423361
Dear Ahmed,
Above Query have error can you fix it and send me again.
0

LVL 8

Expert Comment

ID: 40423576
Dear asrar  and sorry for the mistake:
``````select count(*), devicenum, latitude, longitude from YOUR_TABLE group by devicenum, latitude, longitude, min(systime) FromTime, max(systime) ToTime where speed=0
``````
Wish this solve the problem.
0

Author Comment

ID: 40425796
Thanks dear Ahmed
Can you share your personal e-mail and contact details with me ?
0

LVL 8

Accepted Solution

Ahmed Merghani earned 500 total points
ID: 40428665
No problem. I sent you a connection request @ linkedin.
0

## Featured Post

### Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.