Asrar Azmi
asked on
SQL Query
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
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
ASKER
Dear Expert,
Thanks for your quick reply ,
How we can display systime column also with particular location like Stop Duration Like From To date time.
Waiting your reply.
Thanks for your quick reply ,
How we can display systime column also with particular location like Stop Duration Like From To date time.
Waiting your reply.
How about this:
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
select count(*), latitude, longitude from YOUR_TABLE group by latitude, longitude, min(systime) From, max(systime) To where speed=0
ASKER
Dear Ahmed,
Above Query have error can you fix it and send me again.
Above Query have error can you fix it and send me again.
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.
ASKER
Thanks dear Ahmed
Can you share your personal e-mail and contact details with me ?
Can you share your personal e-mail and contact details with me ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
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:
Open in new window
Put in mind the result is number of minutes as you said the data sending is every minute.