## 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)ASBEGIN    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)ENDGO--Get average daily mileage needed to reach 2000 miles, based on a current mileage-- of 960.2select 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 stringtosearchCREATE FUNCTION findTextInCode (@StringToSearch varchar(100))RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))ASBEGIN   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   RETURNENDGO--Searches column names from stored procedure parameters and function,-- user tables, and view columnsCREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))ASBEGIN   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   RETURNEND`

c
Compose new post
j
Next post/Next comment
k
Previous post/Previous comment
r