Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query

Posted on 2014-11-03
8
Medium Priority
?
94 Views
Last Modified: 2014-11-10
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
Comment
Question by:Asrar Azmi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40418930
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
 

Author Comment

by:Asrar Azmi
ID: 40418953
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40418991
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40419137
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
 

Author Comment

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

Expert Comment

by:Ahmed Merghani
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

Open in new window

Wish this solve the problem.
0
 

Author Comment

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

Accepted Solution

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever stumbled upon a software that is so great that you just love? It happened to me. Love at first sight. Filezilla Server.   Ok its not the most advanced ftp server I've came across. But its a fairly simple piece of software to get the …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question