extrapolation.sql

/*
	Extract per product gradient for y = mx+c, where 	
		y (price or quantity) = m(Slope) * x(Week) + (c)Intercept
*/

DECLARE @Sales TABLE (Week INT, ProductId INT, Quantity INT NULL, Price DECIMAL(18, 2) NULL)
INSERT INTO @Sales VALUES
	(1, 1, 100, 29.99),
	(2, 1, 110, 28.99),
	(3, 1, 120, 27.99),
	(4, 1, 130, 26.99),
	(5, 1, 140, 25.99),
	(6, 1, 150, 24.99),
	(7, 1, 160, 23.99),
	(8, 1, 170, 22.99),
	(9, 1, 180, 21.99),
	(10, 1, NULL, NULL),
	(11, 1, NULL, NULL),
	(12, 1, NULL, NULL),
	(13, 1, NULL, NULL),

	(1, 2, 100, 29.99),
	(2, 2, 110, 28.99),
	(3, 2, 120, NULL),
	(4, 2, 130, 26.99),
	(5, 2, 140, 25.99),
	(6, 2, NULL, 24.99),
	(7, 2, 160, 23.99),
	(8, 2, 170, 22.99),
	(9, 2, 180, 21.99),
	(10, 2, NULL, NULL),
	(11, 2, NULL, NULL),
	(12, 2, NULL, NULL),
	(13, 2, NULL, NULL),
	(14, 2, NULL, NULL),
	(15, 2, NULL, NULL),
	(16, 2, NULL, NULL),
	(17, 2, NULL, NULL),
	(18, 2, NULL, NULL)

DECLARE @Slopes TABLE (ProductId INT, PriceSlope DECIMAL(18, 10), PriceIntercept DECIMAL(18, 10), QuantitySlope DECIMAL(18, 10), QuantityIntercept DECIMAL(18, 10))
INSERT IntO @Slopes
SELECT
	ProductId,
	PriceSlope,
	(p1 - (x1 * PriceSlope)) AS PriceIntercept,
	QuantitySlope,
	(q1 - (x1 * QuantitySlope)) AS QuantityIntercept
FROM
(
	SELECT
		ProductId,
		max(x1) as x1,
		max(p1) as p1,
		max(q1) as q1,		
		sum((x - x1) * (p - p1)) / sum((x - x1) * (x - x1)) as PriceSlope,
		sum((x - x1) * (q - q1)) / sum((x - x1) * (x - x1)) as QuantitySlope
	FROM (
		SELECT
			ProductId,
			AVG([Week]) OVER(PARTITION BY ProductId) AS x1,
			Week AS x,
			AVG(Price) OVER(PARTITION BY ProductId) AS p1,
			Price AS p,
			AVG(Quantity) OVER(PARTITION BY ProductId) AS q1,
			Quantity AS q
		FROM @Sales
		WHERE Price IS NOT NULL
	) AS R
	GROUP BY ProductId
) AS R2

SELECT
	s.Week,
	s.ProductId,
	(CASE WHEN s.Price IS NULL THEN
		((s.Week * g.PriceSlope) + g.PriceIntercept)
	ELSE
		s.Price
	END) AS Price,
	(CASE WHEN s.Quantity IS NULL THEN
		((s.Week * g.QuantitySlope) + g.QuantityIntercept)
	ELSE
		s.Quantity
	END) AS Quantity
FROM @Sales AS s
JOIN @Slopes AS g ON g.ProductId = s.ProductId
ORDER BY ProductId, Week