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
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?
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
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.
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.
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.
svetsarn
Member
487 Points
612 Posts
Calculate time and avg speed?
Apr 24, 2011 10:31 AM|LINK
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?
jerryjoseph
Contributor
6740 Points
1257 Posts
Re: Calculate time and avg speed?
Apr 24, 2011 06:04 PM|LINK
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
linkedin | twitter | www.jerryjoseph.net
svetsarn
Member
487 Points
612 Posts
Re: Calculate time and avg speed?
Apr 24, 2011 06:53 PM|LINK
Thanks for your answer.
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.
jerryjoseph
Contributor
6740 Points
1257 Posts
Re: Calculate time and avg speed?
Apr 24, 2011 07:01 PM|LINK
What is the issue? do you get any error?
linkedin | twitter | www.jerryjoseph.net
svetsarn
Member
487 Points
612 Posts
Re: Calculate time and avg speed?
Apr 25, 2011 06:22 AM|LINK
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.
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Calculate time and avg speed?
Apr 25, 2011 06:46 AM|LINK
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
Kumar Harsh
chohmann
Star
9385 Points
1644 Posts
Re: Calculate time and avg speed?
Apr 25, 2011 05:30 PM|LINK