Calculate average daily mileage required to make yearly goal

This might be useful if you’re programming a running site. For me, however, I wanted to write this calculation in SQL Server to expand my knowledge some.

--Calculates the average daily mileage needed to meet a target yearly mileage goal, based on current mileage.
CREATE FUNCTION averageDistanceNeeded (@targetMileage decimal(10,4), @currentMileage decimal(10,4))
RETURNS decimal(10,4)
Declare @daysLeft decimal(10,4)
Declare @dt datetime

SELECT @dt = dt from openquery
'select dt = getdate()'
select @daysLeft = datediff(day, @dt, CAST (CAST (YEAR(@dt) AS VARCHAR(4)) + '-12-31' AS DATETIME))
return CONVERT(decimal(10,4), (@targetMileage - @currentMileage) / @daysLeft)

--Get average daily mileage needed to reach 2000 miles, based on a current mileage
-- of 960.2
select dbo.averageDistanceNeeded(2000.0, 960.2)

Leave a Reply

%d bloggers like this: