Solved

average speed calculation - Date diff help

Posted on 2014-09-12
12
114 Views
Last Modified: 2014-10-30
Hi,

I need to work out the average speed which is calculated as
Average Speed = distance ÷ time

I know the following
Start Time = '2014-09-12 07:03:36.000'
End Time = '2014-09-12 07:06:22.000'
Distance = 6km

So i need to figure out the SQL which works out the TIME as a float:

I'm having issues figuring out the Time as a float (i.e x.x),
 if i use
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
print convert(float, DATEDIFF(minute,@startTime, @endTime))

Open in new window

This outputs 3   (minutes - but rounded up)

If i change MINUTE to SECOND
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
print convert(float, DATEDIFF(SECOND,@startTime, @endTime))

Open in new window

This Outputs 166 (seconds)

if I try both these calculations to work out average speed: (distance ÷ time):
print 6 / 166   -- this equals 0 (second variation)
print 6 / 3       -- this equals 2 (minute variation)

Open in new window


The problem is the MINUTE variation doesn't take into account seconds, so its rounded UP to 3 mins and its not accurate, and the SECONDS variation just doesn't work

I guess i need to use a combination of both, and some how get the remaining seconds and use as a point
i.e.
Print 6 / 2.8 

Open in new window


however i've no idea how to do this

I guess i need to use the SECOND variation which outputs 166 (seconds) and turn that into a minute variation of 2.X ?

HELP
0
Comment
Question by:websss
[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
  • 2
  • +2
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40318807
to get a end result of "float", you have to get both values converted to float first (though I would rather suggest decimal instead of float)
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @distance int = 6
declare @speed float

print DATEDIFF(SECOND,@startTime, @endTime)
set @speed = cast(DATEDIFF(SECOND,@startTime, @endTime) as float) / ( cast(@distance as float))
                                 

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40318815
what unit of measure do you want as the outcome?

Km/hour
Km/minute
Km/second
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40318817
Is this you are looking for?
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @diff float

SET @diff = DATEDIFF(second,@startTime, @endTime) / 60.0
PRINT 6/@diff 

Open in new window

0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40318828
Why don't you convert to 6000 meters / 166 ?
0
 

Author Comment

by:websss
ID: 40318843
sorry looking for Km/hour
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40318844
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @distance int = 6
declare @speed float

print DATEDIFF(SECOND,@startTime, @endTime)
set @speed = ( cast(@distance as float)) / cast( DATEDIFF(SECOND,@startTime, @endTime) as float)  / 3600.00 
                                 
                                          

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40318852
Km/h?
Then your formula have an error. You are playing only with the 6Km distance.

Adapted my code to Km/h:
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @diff float

SET @diff = DATEDIFF(second,@startTime, @endTime) / 60.0
PRINT CAST((60*6)/@diff AS VARCHAR) + ' Km/h'

Open in new window

0
 

Author Comment

by:websss
ID: 40318858
what result would you all expect to see in KM/h ?

everyones results differ lots!

from 1 km/ph to 130 km/ph
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40318864
I think it's you that should give us the answer for that question.
I took a simple calculation. If it took 166 seconds to make 6Km, then how much Km will do in 60 minutes?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40318866
6/166 = Km/sec * 3600 = Km/Hr

130.1204819277108 Km/Hr
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40318868
Right PortletPaul. It was what I used for my solution.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40318875
if you could run 6 Km in just under 3 minutes would it be very slow or very fast?

3 minutes is 1/20 of an hour so 20*6 Km = 120 Km/Hr

and it has to be more than that figure because 166 seconds is less than 3 minutes (i.e. faster)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

724 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