Forecasting in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Forecasting in SQL

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-27 : 14:23:48

I have weekly table that I would like to append a forecast column to
showing what would be numerical forecast for each Fullname in 6 mos from now and 12 mos from now. Excel has FORECAST function to predict future value. I was wondering if thsi can be done in SQL. (I could not find a free .net C# forecasting library). Thank you

Fullname 6/23/2012 6/16/2012 6/9/2012 6/2/2012
onwisxa02:/onwisxa02_prd_global 22.51 22.09 21.81 21.55
456frsaa01:/456frsaa01_vol1 82.66 82.76 82.89 82.16
456frsaa02:/456frsaa02_prd_global 22.5 22.08 21.81 21.54
tyyerana01:/tyyerana01_ret_relc_prd3 31.23 31.13 31.08 31.01
tyyerana01:/tyyerana01__prod 72.55 72.54 72.52 72.5
tyyerana02:/tyyerana02_prd_01 68.58 68.4 68.12 67.56
tyyerana03:/tyyerana03_ret_prd NULL NULL NULL NULL
tyyerana04:/tyyerana04_na_home30 3.08 3.07 3.07 3.07
uerw330:/uerw330_uerw330_logs NULL NULL NULL NULL
just452:/just45200_AR_VOL4 0 0 0 0
just452:/just45200_AR_VOL5 0 0 0 0
just452:/BCFL01_vol4 62.6 62.45 63.04 62.01
just452:/BCFL01_vol7 49.64 60.63 60.43 60.15

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-27 : 15:31:59

Looking at the Excel help file I duplicated the logic in SQL:

DECLARE @t TABLE(y TINYINT, x TINYINT)
INSERT @t VALUES(6,20),(7,28),(9,31),(15,38),(21,40)		-- x and y values
;WITH cte(x_,y_) AS (SELECT AVG(x*1.0), AVG(y*1.0) FROM @t)	-- average of x and y (x_, y_)
, c2 AS (SELECT SUM((x-x_)*(y-y_))/SUM(POWER(x-x_,2)) b
	FROM cte CROSS JOIN @t)					-- sum of variances = b
, c3 AS (SELECT y_-(b*x_) a, b FROM c2 CROSS JOIN cte)		-- a = y_-bx_
, x(x) AS (SELECT 30)						-- put your forecast X value(s) here
SELECT x, a+(b*x) Forecast FROM c3 CROSS JOIN x_value

edit: made slight change to formula

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-28 : 09:27:01

not sure if I follow, can you please show me an example using my data set? TY

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-28 : 09:52:48

I could but I have no idea which values are X and which are Y. If the dates are the X values you'll have to convert them to numbers for this to work correctly.

Can you run the forecast in Excel for one value and post how you set it up?

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-29 : 13:41:25

Yes, I need to mark dates with integers. Looking at Excel sheet
this is the current logic...

6/23/2012 has value of 22
6/16/2012 has value of 15
6/9/2012 has value of 8
6/2/2012 has value of 1 ( we need to stamp 4th week in the past always with value 1)

The first monthly forecast out of 12, would be for 7/24/2012 from date 6/23/2012 or 52 days after last value from the data. 22 + 30 = 52

FORECAST (52, range 22 ....1 values, range 22 15 ... 1)

Thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-29 : 15:39:43

This is going to look ugly but it matches the output I got from Excel based on the input you gave:

DECLARE @t TABLE(d DATE, y INT, x AS DATEDIFF(DAY,0,d))
INSERT @t(d,y) VALUES('6/23/2012',22),('6/16/2012',15),('6/9/2012',8),('6/2/2012',1)

;WITH cte(x_,y_) AS (SELECT AVG(x*1.0), AVG(y*1.0) FROM @t)	-- average of x and y (x_, y_)
, c2 AS (SELECT SUM((x-x_)*(y-y_))/SUM(POWER(x-x_,2)) b
	FROM cte CROSS JOIN @t)					-- sum of variances = b
, c3 AS (SELECT y_-(b*x_) a, b FROM c2 CROSS JOIN cte)		-- a = y_-bx_
, d_value(d) AS (SELECT CAST('7/24/2012' AS DATE))		-- put your forecast date value(s) here
, x_value(x) AS (SELECT DATEDIFF(DAY,0,d) FROM d_value)
SELECT DATEADD(DAY,0,x) x, a+(b*x) Forecast FROM c3 CROSS JOIN x_value

I got 53 as the forecast value for 7/24/2012. Taking this change I applied it to the data you originally posted:

DECLARE @t2 TABLE(Fullname VARCHAR(50),	[6/23/2012] MONEY, [6/16/2012] MONEY, [6/9/2012] MONEY, [6/2/2012] MONEY)
INSERT @t2 VALUES('onwisxa02:/onwisxa02_prd_global',22.51,22.09,21.81,21.55),
('456frsaa01:/456frsaa01_vol1',82.66,82.76,82.89,82.16),
('456frsaa02:/456frsaa02_prd_global',22.5,22.08,21.81,21.54),
('tyyerana01:/tyyerana01_ret_relc_prd3',31.23,31.13,31.08,31.01),
('tyyerana01:/tyyerana01__prod',72.55,72.54,72.52,72.5),
('tyyerana02:/tyyerana02_prd_01',68.58,68.4,68.12,67.56),
('tyyerana03:/tyyerana03_ret_prd',NULL,NULL,NULL,NULL),
('tyyerana04:/tyyerana04_na_home30',3.08,3.07,3.07,3.07),
('uerw330:/uerw330_uerw330_logs',NULL,NULL,NULL,NULL),
('just452:/just45200_AR_VOL4',0,0,0,0),
('just452:/just45200_AR_VOL5',0,0,0,0),
('just452:/BCFL01_vol4',62.6,62.45,63.04,62.01),
('just452:/BCFL01_vol7',49.64,60.63,60.43,60.15)

;WITH vals AS (SELECT FullName, CAST(d_value as date) d_value, y, DATEDIFF(DAY,0,d_value) x FROM @t2 a
	UNPIVOT(y FOR d_value IN ([6/23/2012],[6/16/2012],[6/9/2012],[6/2/2012])) b)
, cte(FullName, x_,y_) AS (SELECT FullName, AVG(x*1.0), AVG(y*1.0) FROM vals GROUP BY FullName)
, c2 AS (SELECT cte.FullName, SUM((x-x_)*(y-y_))/SUM(POWER(x-x_,2)) b
	FROM cte INNER JOIN vals ON cte.FullName=vals.Fullname GROUP BY cte.FullName)
, c3 AS (SELECT cte.FullName, y_-(b*x_) a, b FROM c2 INNER JOIN cte ON c2.FullName=cte.FullName)
, d_value(d) AS (SELECT CAST('7/24/2012' AS DATE))
, x_value(x) AS (SELECT DATEDIFF(DAY,0,d) FROM d_value)
SELECT c3.FullName, DATEADD(DAY,0,x) x, a+(b*x) Forecast FROM c3 CROSS JOIN x_value

I had to unpivot it because of the way you had the data. If you're storing it that way I recommend you change the table structure, that way you won't have to unpivot and modify date columns, etc.

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-29 : 17:33:40

This is great.... THANK YOU!. Getting very close...

I unpivoted the data and now my table looks like this:
How would I apply your logic to forecast the data from this table? I don't fully understand CTE

FullName Date PercentCol
onwisxa02:/onwisxa02_prd_global 6/23/2012 22.51
onwisxa02:/onwisxa02_prd_global 6/16/2012 22.09
onwisxa02:/onwisxa02_prd_global 6/9/2012 21.81
onwisxa02:/onwisxa02_prd_global 6/2/2012 21.55
onwisxa02:/onwisxa02_prd_global 5/26/2012 21.25
456frsaa01:/456frsaa01_vol1 6/23/2012 82.66
456frsaa01:/456frsaa01_vol1 6/16/2012 82.76
456frsaa01:/456frsaa01_vol1 6/9/2012 82.89
456frsaa01:/456frsaa01_vol1 6/2/2012 82.16
456frsaa01:/456frsaa01_vol1 5/26/2012 81.05
456frsaa02:/456frsaa02_prd_global 6/23/2012 22.5
456frsaa02:/456frsaa02_prd_global 6/16/2012 22.08
456frsaa02:/456frsaa02_prd_global 6/9/2012 21.81
456frsaa02:/456frsaa02_prd_global 6/2/2012 21.54
456frsaa02:/456frsaa02_prd_global 5/26/2012 21.31

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-29 : 18:30:46

The UNPIVOT is only there to restructure date columns to rows, you can remove the entire "vals" section from the CTE. The remaining code works like the first example, it translates dates into integers and feeds them to the forecasting formula. All you need to do is substitute your table and column names (d_value->date, y->PercentCol, @t2->your table name).

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-30 : 17:18:09

I got to the point when I can run the query agianst my data set but I am not able to unpivot it ( want to get rid of dependency on the column names). I also inserted nullif statemnt since I was getting divide by zero error on forecast. Can you please show me how to take out the UNPIVOT section (assume the data set looks like the second table I shown. Appreciate all your help.

Latest working query:
;WITH vals AS (SELECT FullName, CAST(rundate as date) rundate, [% Used], DATEDIFF(DAY,0,rundate) x FROM dbo.TEST a
UNPIVOT([% Used] FOR rundate IN ([06/29/2012],[06/23/2012],[06/16/2012],[06/09/2012])) b)
, cte(FullName, x_,y_) AS (SELECT FullName, AVG(x*1.0), AVG([% Used]*1.0) FROM vals GROUP BY FullName)
, c2 AS (SELECT cte.FullName, SUM((x-x_)*([% Used]-y_))/nullif(SUM(POWER(x-x_,2)),0.00) b
FROM cte INNER JOIN vals ON cte.FullName=vals.Fullname GROUP BY cte.FullName)
, c3 AS (SELECT cte.FullName, y_-(b*x_) a, b FROM c2 INNER JOIN cte ON c2.FullName=cte.FullName)
, rundate(d) AS (SELECT CAST('7/29/2012' AS DATE))
, x_value(x) AS (SELECT DATEDIFF(DAY,0,d) FROM rundate)
SELECT c3.FullName, DATEADD(DAY,0,x) x, a+(b*x) Forecast FROM c3 CROSS JOIN x_value

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-30 : 19:24:09

[code];WITH vals AS (SELECT FullName, [% Used], DATEDIFF(DAY,0,date) x FROM dbo.TEST a)
, cte(FullName, x_,y_) AS (SELECT FullName, AVG(x*1.0), AVG([% Used]*1.0) FROM vals GROUP BY FullName)
, c2 AS (SELECT cte.FullName, SUM((x-x_)*([% Used]-y_))/nullif(SUM(POWER(x-x_,2)),0.00) b
FROM cte INNER JOIN vals ON cte.FullName=vals.Fullname GROUP BY cte.FullName)
, c3 AS (SELECT cte.FullName, y_-(b*x_) a, b FROM c2 INNER JOIN cte ON c2.FullName=cte.FullName)
, rundate(d) AS (SELECT CAST('7/29/2012' AS DATE))
, x_value(x) AS (SELECT DATEDIFF(DAY,0,d) FROM rundate)
SELECT c3.FullName, DATEADD(DAY,0,x) x, a+(b*x) Forecast FROM c3 CROSS JOIN x_value[/code]That's the best I can do without actual DDL.

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-07-01 : 08:08:08

Thank you robvolk, it works great. Just changed DATEDIFF(DAY,0,date) to DATEDIFF(DAY,0,rundate)., put all of this in a view and left join on original pivoted data giving me what I wanted.

THANK YOU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources