Need Help Writing a SQL Query that can provide a 6 Month Forecast

Question

Need Help Writing a SQL Query that can provide a 6 Month Forecast

Lee Polikoff

Mr or Mrs. 500

Points: 576
More actions
December 17, 2014 at 12:23 pm

#292905

Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
Period---------Date--------------------------------Percent
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
7--------------2014-05-01 00:00:00.000---------0.3874
8--------------2014-06-01 00:00:00.000---------0.4067
9--------------2014-07-01 00:00:00.000---------0.4623
10------------2014-08-01 00:00:00.000---------0.412
11------------2014-09-01 00:00:00.000---------0.4155
12------------2014-10-01 00:00:00.000---------0.3929
13------------2014-11-01 00:00:00.000---------0.4038
I need to forecast the next 6 dates 12/1/2014 thru 5/1/2015 using the last 6 months in the data set (periods 8 thru 13)
Period---------Date--------------------------------Percent-------Forecast
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
7--------------2014-05-01 00:00:00.000---------0.3874
8--------------2014-06-01 00:00:00.000---------0.4067
9--------------2014-07-01 00:00:00.000---------0.4623
10-------------2014-08-01 00:00:00.000---------0.412
11-------------2014-09-01 00:00:00.000---------0.4155
12-------------2014-10-01 00:00:00.000---------0.3929
13-------------2014-11-01 00:00:00.000---------0.4038
14-------------2014-12-01 00:00:00.000--------------------------0.3936
15-------------2015-01-01 00:00:00.000--------------------------0.3874
16-------------2015-02-01 00:00:00.000--------------------------0.3811
17-------------2015-03-01 00:00:00.000--------------------------0.3748
18-------------2015-04-01 00:00:00.000--------------------------0.3686
19-------------2015-05-01 00:00:00.000--------------------------0.3623
I've looked at other queries that people wrote, but they are confusing me. I was hoping someone can explain how to use the first table to generate the forecast values in the second table.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Lee Polikoff (12/17/2014)
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
Period---------Date--------------------------------Percent
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
7--------------2014-05-01 00:00:00.000---------0.3874
8--------------2014-06-01 00:00:00.000---------0.4067
9--------------2014-07-01 00:00:00.000---------0.4623
10------------2014-08-01 00:00:00.000---------0.412
11------------2014-09-01 00:00:00.000---------0.4155
12------------2014-10-01 00:00:00.000---------0.3929
13------------2014-11-01 00:00:00.000---------0.4038
I need to forecast the next 6 dates 12/1/2014 thru 5/1/2015 using the last 6 months in the data set (periods 8 thru 13)
Period---------Date--------------------------------Percent-------Forecast
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
7--------------2014-05-01 00:00:00.000---------0.3874
8--------------2014-06-01 00:00:00.000---------0.4067
9--------------2014-07-01 00:00:00.000---------0.4623
10-------------2014-08-01 00:00:00.000---------0.412
11-------------2014-09-01 00:00:00.000---------0.4155
12-------------2014-10-01 00:00:00.000---------0.3929
13-------------2014-11-01 00:00:00.000---------0.4038
14-------------2014-12-01 00:00:00.000--------------------------0.3936
15-------------2015-01-01 00:00:00.000--------------------------0.3874
16-------------2015-02-01 00:00:00.000--------------------------0.3811
17-------------2015-03-01 00:00:00.000--------------------------0.3748
18-------------2015-04-01 00:00:00.000--------------------------0.3686
19-------------2015-05-01 00:00:00.000--------------------------0.3623
I've looked at other queries that people wrote, but they are confusing me. I was hoping someone can explain how to use the first table to generate the forecast values in the second table.

I understand that you're using the previous six months but what is the basis? For example, what is the formula that you used to come up with the value for month 14? I've tried a couple of different methods and didn't come up with the same answer as what you posted.

Also, please see the article at the first link under "Helpful Links" in my signature line below. Most people I know want to test their code with your data and doing the things in that article will likely get you better help and a coded response much more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lee Polikoff Mr or Mrs. 500 Points: 576 More actions · Answer 2

I stuck the values in Excel and used the FORECAST function.

The X value was period 14

Known X's were periods 8 thru 13

Know Y's were the pcts for periods 8 thru 13.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

Look at Forecasting with SQL By Mark Wojciechowicz,

http://www.sqlservercentral.com/articles/T-SQL/69334/

Lee Polikoff Mr or Mrs. 500 Points: 576 More actions · Answer 4

That was PERFECT! 😀 Thanks for pointing me to that link. I was able to generate the exact same trend values as Excel reported.