Solved

average speed calculation - Date diff help

Posted on 2014-09-12
12
108 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
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 142

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 48

Expert Comment

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

Km/hour
Km/minute
Km/second
0
 
LVL 46

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

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 46

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 48

Expert Comment

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

130.1204819277108 Km/Hr
0
 
LVL 46

Expert Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now