Solved

average speed calculation - Date diff help

Posted on 2014-09-12
12
106 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 45

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 82

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 45

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 45

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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

12 Experts available now in Live!

Get 1:1 Help Now