Solved

# average speed calculation - Date diff help

Posted on 2014-09-12
110 Views
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))
``````
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))
``````
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)
``````

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
``````

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
Question by:websss
• 4
• 3
• 2
• +2

LVL 143

Expert Comment

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))

``````
0

LVL 48

Expert Comment

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

Km/hour
Km/minute
Km/second
0

LVL 48

Expert Comment

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
``````
0

LVL 83

Expert Comment

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

Author Comment

ID: 40318843
sorry looking for Km/hour
0

LVL 143

Expert Comment

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

``````
0

LVL 48

Accepted Solution

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.

``````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'
``````
0

Author Comment

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 48

Expert Comment

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 48

Expert Comment

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

130.1204819277108 Km/Hr
0

LVL 48

Expert Comment

ID: 40318868
Right PortletPaul. It was what I used for my solution.
0

LVL 48

Expert Comment

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

Question has a verified solution.

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