Calculate average daily mileage required to make yearly goalPosted: September 11, 2007 | Author: tech0x20 | Filed under: sql server | Leave a comment »
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))
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)