Solved

average speed calculation - Date diff help

Posted on 2014-09-12
12
111 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 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 49

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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 49

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 49

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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 60
SQL Consolidate rows 3 25
SSIS GUID Variable 2 31
help converting varchar to date 14 25
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

740 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