Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

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
0
Asrar Azmi
Asked:
Asrar Azmi
  • 4
  • 3
1 Solution
 
Ahmed MerghaniCommented:
Your query will be something like:
select count(*), latitude, longitude from YOUR_TABLE group by latitude, longitude where speed=0

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:
select count(*) from YOUR_TABLE  where speed=0

Open in new window

Put in mind the result is number of minutes as you said the data sending is every minute.
0
 
Asrar AzmiAuthor Commented:
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.
0
 
Phillip BurtonCommented:
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

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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

Open in new window

0
 
Asrar AzmiAuthor Commented:
Dear Ahmed,
Above Query have error can you fix it and send me again.
0
 
Ahmed MerghaniCommented:
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

Open in new window

Wish this solve the problem.
0
 
Asrar AzmiAuthor Commented:
Thanks dear Ahmed
Can you share your personal e-mail and contact details with me ?
0
 
Ahmed MerghaniCommented:
No problem. I sent you a connection request @ linkedin.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now