Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Forecasting with SQL Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 9:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
Thanks for the clarifications, Al. I appreciate the finer points and I am sure this will be helpful to any readers as well.
Mark
Post #862914
Posted Wednesday, February 10, 2010 5:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 28, 2013 7:44 AM
Points: 11, Visits: 191
Do you have any useful info on calculating multiple linear regression with T-SQL?
Post #863130
Posted Wednesday, February 10, 2010 12:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:48 PM
Points: 2,397, Visits: 3,406
piet_dj (2/10/2010)
Do you have any useful info on calculating multiple linear regression with T-SQL?

Here http://developerworkshop.net/software.html
It also handles logarithmic, exponential and power regression.



N 56°04'39.16"
E 12°55'05.25"
Post #863568
Posted Thursday, February 11, 2010 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 23, 2010 9:12 AM
Points: 1, Visits: 13
Very good article, thanks. When I picked up the code snippet and tried to use it, I saw one problem. In the last section of code, labeled "Create Forecast", the term that's multiplied by the seasonality factor is missing a parenthesis. That is,
MAX(A) + (MAX(B) * MAX(Forecastkey) + 1) should be
(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))

Correct me if I'm wrong, but this is the change that it took to make my forecast look correct.
Post #864067
Posted Thursday, February 11, 2010 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
ltaylor 73774 (2/11/2010)
Very good article, thanks. When I picked up the code snippet and tried to use it, I saw one problem. In the last section of code, labeled "Create Forecast", the term that's multiplied by the seasonality factor is missing a parenthesis. That is,
MAX(A) + (MAX(B) * MAX(Forecastkey) + 1) should be
(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))

Correct me if I'm wrong, but this is the change that it took to make my forecast look correct.


Yes, you are correct. When the trend is multiplied against seasonality, the parenthese are needed.

Thank you for finding that error! I will get the download updated.

Mark
Post #864148
Posted Wednesday, February 17, 2010 1:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
Allen Nugent (2/9/2010)
Nice work, Mark.
I'll second that - a very nice article indeed.
Of course, there are weird and wonderful techniques for fitting data to nonlinear functions, but it might not be practical to do it with SQL.
Well, if you can do it say in Fortran+Dystal+LAPACK+FSPAK it's probably not too hard in SQL. What sort of package do people generally use for this sort of thing today? I'm way out of date (forgotten nearly all of my stats and most of my curve fitting) so I wouldn't try to do it myself. But if I asked an expert for predictions I would definitely want to know what methods he used, what assumptions he made, and maybe look at the code to check it didn't include the most common idiocies (and SQL is a language I can read, because it was designed for readability rather than to enable maximum obscurity like some pouplar languages).


Tom
Post #867459
Posted Friday, February 26, 2010 5:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:12 AM
Points: 60, Visits: 903
Excellent article. I have a lot of forecast and actual time-series data and will check out the methods this weekend.
Post #873349
Posted Friday, May 6, 2011 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:27 PM
Points: 16, Visits: 5
Mark,

Very informative and useful article,

Do you have any suggestion on how to incorporate a weighting element into the SQL. i.e. if i wanted to give more weight to last years data over the year before etc.



Post #1104742
Posted Friday, May 27, 2011 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
Hi Grasshopper,
I do not have any examples of weighted averages, but you may considering looking at exponential smoothing which weights, more heavily, recent periods.
I do suggest making sure that you prepare the data before forecasting on it. By using a moving median, you can eliminate a lot of the outliers.
Also, it's worth looking at the forecasting datamining algorithm in SSAS, which is a great tool.

Good luck,
Mark
Post #1116501
Posted Saturday, May 28, 2011 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:48 PM
Points: 2,397, Visits: 3,406
Here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 is examples of both moving average, weighted average and weighted moving average.




N 56°04'39.16"
E 12°55'05.25"
Post #1116597
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse