Linear Regression Forecast

-- =============================================================================
-- Author:Silva, Mike
-- Create date: 7/11/2009
-- Description:Calculates the same formula as MS Excel Forecast function
-- Caution:This function is dependent on three things
--1. A valid input value for the prediction
--2. Both delimited x and y input values MUST contain numeric data
-- Example:
--PRINT dbo.ufn_LinearForecast(0, '552,6:313,5:1213,4:1204,3:721,2:428,1')
-- =============================================================================
ALTER FUNCTION [dbo].[ufn_LinearForecast]
(
@Predictorfloat,
@DelimitedXYvaluesvarchar(8000)
)
RETURNS float
AS
BEGIN
DECLARE @yDelimnvarchar (1),
@xDelimnvarchar (1),
@NextSetint,
@SetCountint,
@yPosint,
@xPosint,
@yValvarchar(10),
@xValvarchar(10),
@Valuesvarchar(100),
@sigmaXfloat,
@sigmaYfloat,
@sigmaXXfloat,
@sigmaXYfloat,
@sigmaYYfloat,
@regSlopefloat,
@regYIntfloat,
@valuefloat
--Initialize 
SET @yDelim = ','
SET @xDelim = ':'
SET @sigmaX = 0
SET @sigmaY = 0
SET @sigmaXX = 0
SET @sigmaXY = 0
SET @sigmaYY = 0

--Check for trailing delimiter, if it doesn't exist then add it
IF (RIGHT(@DelimitedXYvalues,1)<> @xDelim)
SET @DelimitedXYvalues = @DelimitedXYvalues + @xDelim

--Get position of first xDelim
SET @xPos = CHARINDEX(@xDelim,@DelimitedXYvalues)
SET @NextSet = 1
SET @SetCount = 0

--Loop while there is still an x delimiter in the string
WHILE (@xPos <> 0)
BEGIN
SET @SetCount = @SetCount + 1
SET @Values  = SUBSTRING(@DelimitedXYvalues,1,@xPos -1)
SET @yPos = CHARINDEX(@yDelim,@DelimitedXYvalues)
SET @yVal = SUBSTRING(@Values,1,@yPos -1)
SET @xVal = SUBSTRING(@Values,@yPos + 1, LEN(@Values)-1)

--Get the sums of X, Y, X*Y, and X^2
SET @sigmaXY= @sigmaXY + (CAST(@xVal as float) * CAST(@yVal as float))
SET @sigmaXX= @sigmaXX + POWER(CAST(@xVal as float), 2)
SET @sigmaYY= @sigmaYY + POWER(CAST(@yVal as float), 2)
SET @sigmaX= @sigmaX + @xVal
SET @sigmaY= @sigmaY + @yVal

SET @NextSet = @xPos + 1
SET @DelimitedXYvalues = SUBSTRING(@DelimitedXYvalues,@NextSet,LEN(@DelimitedXYvalues))
SET @xPos = CHARINDEX(@xDelim, @DelimitedXYvalues)
END

--  Now we need to determine what the slope of the regression line will be
--  Slope(b) = NÓXY - (ÓX)(ÓY) / (NÓX2 - (ÓX)2)
SET @regSlope= ((@SetCount * @sigmaXY) - (@sigmaX * @sigmaY)) / ((@SetCount * @sigmaXX) - POWER(@sigmaX, 2))

--  Next we need to determine what the point of Y intercept is
--  Intercept(a) = (ÓY - b(ÓX)) / N 
SET @regYInt= (@sigmaY - (@regSlope * @sigmaX)) / @SetCount

--  Now use slope and intercept and predictor value in regression equation
--  Regression Equation(y) = a + bx
SET @value= @regYInt + (@regSlope * @Predictor)

RETURN @value
END