1997 Toyota Corolla Parts Store

Toyota Parts and Books on Amazon.com

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)
AS
BEGIN
Declare @daysLeft decimal(10,4)
Declare @dt datetime

SELECT @dt = dt from openquery
(
"my-server",
'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)
END
GO

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

Further refined version of code searching

I have now included views in the search code version. Both functions now return the object type the result was found in.


--Searches stored procedures, functions and views for code
-- containing the stringtosearch
CREATE FUNCTION findTextInCode (@StringToSearch varchar(100))
RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))
AS
BEGIN
SET @StringToSearch = '%' + @StringToSearch +'%'
INSERT @fNames
SELECT DISTINCT @stringtosearch, SO.Name, SO.Type
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
AND (SO.Type = 'P' OR SO.Type LIKE '%F%' OR SO.Type = 'V')
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
RETURN
END
GO


--Searches column names from stored procedure parameters and function,
-- user tables, and view columns
CREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))
RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))
AS
BEGIN
SET @StringToSEarch = '%' + @StringToSearch + '%'
INSERT @fColumnNames
SELECT DISTINCT SO.NAME, SC.NAME, SO.TYPE
FROM syscolumns SC (NOLOCK)
INNER JOIN
sysobjects SO (NOLOCK)
ON SC.id = SO.id
WHERE SC.NAME LIKE @StringToSearch
ORDER BY SC.Name
RETURN
END