# Calculate time and avg speed? RSS

## 6 replies

Last post Apr 25, 2011 05:30 PM by chohmann

Member

487 Points

612 Posts

### Calculate time and avg speed?

Hey

I have a table where I store data about trips, now I need to calculate time and avg speed between two stops on the trip. I can't get this to work. My table looks like this

TourId, ActionDateStart(datetime2), ActionDateEnd(datetime2), Action(varchar), MilesStart(int), MilesEnd(int), MilesTotal(computed MilesEnd - MilesStart), TotalTimeForAction, AvgSpeed

I can calculate the time in hours and minutes with this:
((CONVERT([varchar],datediff(hour,[ActionDateStart],[ActionDateEnd]),(0))+':')+CONVERT([varchar],datediff(minute,[ActionDateStart],[ActionDateEnd])%(60),0)) I have this code in the computed column spec.

But how can I calculate the avg speed in the AvgSpeed column? One thin more I have problem with, if the hours are 3 and the minutes are 5 it will shows as 3:5, I want it to show up as 3:05. How can I do that?

I maybe have to use function or SP?

Contributor

6740 Points

1257 Posts

### Re: Calculate time and avg speed?

I would recommend something like this.

In TotalTimeForAction you should store the time difference in seconds.
Something like CONVERT([int],datediff(minute,[ActionDateStart],[ActionDateEnd]),0)

Displaying this in a format like 3:05 is presentation logic which should not be handled inside SP.

Once you have the time difference in minutes you can easily calculate average speed as MilesTotal/TotalTimeForAction

Jerry Joseph - MCPD, MCTS

Member

487 Points

612 Posts

### Re: Calculate time and avg speed?

I forgot to write that MilesTotal is in km not miles. I have tried to calculate it this way:
((MilesEnd - MilesStart)*1000) / DATEDIFF(second, ActionDateStart, ActionDateEnd)*3.6

In my calculation I convert the km to meters and then I get the meters per second, then I multiply it with 3.6 to get km/h. But this will not work. I have this in the computed column spec.

Contributor

6740 Points

1257 Posts

### Re: Calculate time and avg speed?

#### svetsarn

I forgot to write that MilesTotal is in km not miles. I have tried to calculate it this way:
((MilesEnd - MilesStart)*1000) / DATEDIFF(second, ActionDateStart, ActionDateEnd)*3.6

In my calculation I convert the km to meters and then I get the meters per second, then I multiply it with 3.6 to get km/h. But this will not work. I have this in the computed column spec.

What is the issue? do you get any error?

Jerry Joseph - MCPD, MCTS

Member

487 Points

612 Posts

### Re: Calculate time and avg speed?

I have these values in the db.

ActionDateStart = 2011-04-21 08:00:00.0000000
ActionDateEnd = 2011-04-21 09:30:00.0000000
MileStart = 314841
MileEnd = 314884

I should have an avg speed of 29 km/h, but I get 25.2 km/h.

I think the problem is this. When I divide the km with the seconds I get the answer 7, it's should be 7,96. So the problem is why I get an int and not a decimal, I think.

I can do the calclation in the SP but I like to have it in the computed column spec.

All-Star

15133 Points

3647 Posts

### Re: Calculate time and avg speed?

Yes it is good to hv it as Computed Column.
Keep the data type as decimal or float.
Create a function that convert second into hh:mm

Declare @ActionDateStart datetime
set @ActionDateStart = '2011-04-21 08:00:00'
Declare @ActionDateEnd datetime
Declare @MileStart float
Declare @MileEnd float
set @ActionDateEnd = '2011-04-21 09:30:00'
set @MileStart = 314841
set @MileEnd = 314884

--Select (@MileEnd-@MileStart)/datediff(second,@ActionDateStart,@ActionDateEnd)

Declare @time float
set @time= convert(varchar,datediff(second,@ActionDateStart,@ActionDateEnd)/(60*60))+'.'+convert(varchar,datediff(second,@ActionDateStart,@ActionDateEnd)%(60*60))

Select @time
Select (@MileEnd-@MileStart)/@time

Warm Regards
Kumar Harsh

Star

9385 Points

1644 Posts

### Re: Calculate time and avg speed?

```SELECT *, CAST(ROUND((MilesEnd - MilesStart)*3600.0/DATEDIFF(SECOND,ActionDateStart,ActionDateEnd),0) AS INT) AS AvgSpeed