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]
(
	@Predictor			float,
	@DelimitedXYvalues	varchar(8000)
)
RETURNS float
AS
BEGIN
	DECLARE @yDelim		nvarchar (1),
			@xDelim		nvarchar (1),
			@NextSet	int,
			@SetCount	int,
			@yPos		int,
			@xPos		int,
			@yVal		varchar(10),
			@xVal		varchar(10),
			@Values		varchar(100),
			@sigmaX		float,
			@sigmaY		float,
			@sigmaXX	float,
			@sigmaXY	float,
			@sigmaYY	float,
			@regSlope	float,
			@regYInt	float,
			@value		float
	--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