-- Prepare test data
CREATE TABLE	cf
		(
			x decimal(38, 10),
			y decimal(38, 10)
		) 

-- Calculate Linear regression
INSERT	cf
SELECT	40.5, 104.5 UNION ALL
SELECT	38.6, 102 UNION ALL
SELECT	37.9, 100 UNION ALL
SELECT	36.2, 97.5 UNION ALL
SELECT	35.1, 95.5 UNION ALL
SELECT	34.6, 94

SELECT	'Linear regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Exponential regression
DELETE
FROM	cf

INSERT	cf
SELECT	.72, 2.16 UNION ALL
SELECT	1.31, 1.61 UNION ALL
SELECT	1.95, 1.16 UNION ALL
SELECT	2.58, .85 UNION ALL
SELECT	3.14, .5

SELECT	'Exponential regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Logarithmic regression
DELETE
FROM	cf

INSERT	cf
SELECT	3, 1.5 UNION ALL
SELECT	4, 9.3 UNION ALL
SELECT	6, 23.4 UNION ALL
SELECT	10, 45.8 UNION ALL
SELECT	12, 60.1

SELECT	'Logarithmic regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

-- Calculate Power regression
DELETE
FROM	cf

INSERT	cf
SELECT	10, .95 UNION ALL
SELECT	12, 1.05 UNION ALL
SELECT	15, 1.25 UNION ALL
SELECT	17, 1.41 UNION ALL
SELECT	20, 1.73 UNION ALL
SELECT	22, 2 UNION ALL
SELECT	25, 2.53 UNION ALL
SELECT	27, 2.98 UNION ALL
SELECT	30, 3.85 UNION ALL
SELECT	32, 4.59 UNION ALL
SELECT	35, 6.02

SELECT	'Power regression' AS Type, A, b, R2
FROM	dbo.fnCurveFitting(1)
UNION ALL
SELECT	'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROM	dbo.fnBestFit()

DROP TABLE cf

CREATE FUNCTION dbo.fnCurveFitting
(
	@Type TINYINT
)
RETURNS @p TABLE (A DECIMAL(38, 10), b DECIMAL(38, 10), R2 DECIMAL(38, 10))
AS
/*
Type = 1	Linear		y = a + b*x
Type = 2	Exponential	y = a*e^(b*x)   nb a > 0
Type = 3	Logarithmic	y = a + b*ln(x)
Type = 4	Power		y = a*x^b	nb a > 0
*/
BEGIN
	DECLARE	@n DECIMAL(38, 10),
		@x DECIMAL(38, 10),
		@x2 DECIMAL(38, 10),
		@y DECIMAL(38, 10),
		@xy DECIMAL(38, 10),
		@y2 DECIMAL(38, 10),
		@d DECIMAL(38, 10),
		@a DECIMAL(38, 10),
		@b DECIMAL(38, 10),
		@r2 DECIMAL(38, 10)

	SELECT	@n =	COUNT(*),
		@x =	CASE
				WHEN @Type = 2 THEN SUM(x)
				WHEN @Type = 3 THEN SUM(LOG(x))
				WHEN @Type = 4 THEN SUM(LOG(x))
				ELSE SUM(x)
			END,
		@x2 =	CASE
				WHEN @Type = 2 THEN SUM(x * x)
				WHEN @Type = 3 THEN SUM(LOG(x) * LOG(x))
				WHEN @Type = 4 THEN SUM(LOG(x) * LOG(x))
				ELSE SUM(x * x)
			END,
		@y =	CASE
				WHEN @Type = 2 THEN SUM(LOG(y))
				WHEN @Type = 3 THEN SUM(y)
				WHEN @Type = 4 THEN SUM(LOG(y))
				ELSE SUM(y)
			END,
		@xy =	CASE
				WHEN @Type = 2 THEN SUM(x * LOG(y))
				WHEN @Type = 3 THEN SUM(LOG(x) * y)
				WHEN @Type = 4 THEN SUM(LOG(x) * LOG(y))
				ELSE SUM(x * y)
			END,
		@y2 =	CASE
				WHEN @Type = 2 THEN SUM(LOG(y) * LOG(y))
				WHEN @Type = 3 THEN SUM(y * y)
				WHEN @Type = 4 THEN SUM(LOG(y) * LOG(y))
				ELSE SUM(y * y)
			END,
		@d =	@n * @x2 - @x * @x		
	FROM	cf

	IF @d = 0
		RETURN

	SELECT	@a = (@x2 * @y - @x * @xy) / @d,
		@b = (@n * @xy - @x * @y) / @d,
		@r2 = (@a * @y + @b * @xy - @y * @y / @n) / (@y2 - @y * @y / @n)

	INSERT	@p
	SELECT	CASE
			WHEN @Type = 2 THEN EXP(@a)
			WHEN @Type = 3 THEN @a
			WHEN @Type = 4 THEN EXP(@a)
			ELSE @a
		END,
		@b,
		@r2

	RETURN
END

CREATE FUNCTION dbo.fnBestFit
(
)
RETURNS @p TABLE (Type TINYINT, A DECIMAL(38, 10), b DECIMAL(38, 10), R2 DECIMAL(38, 10))
AS

BEGIN
	INSERT	@p
	SELECT	1,
		A,
		b,
		R2
	FROM	dbo.fnCurveFitting(1)

	INSERT	@p
	SELECT	2,
		A,
		b,
		R2
	FROM	dbo.fnCurveFitting(2)

	INSERT	@p
	SELECT	3,
		A,
		b,
		R2
	FROM	dbo.fnCurveFitting(3)

	INSERT	@p
	SELECT	4,
		A,
		b,
		R2
	FROM	dbo.fnCurveFitting(4)

	DELETE
	FROM	@p
	WHERE	R2 <> (SELECT MAX(R2) FROM @p)

	RETURN
END