## Forecasting with SQL

 Author Message mark.wojciechowicz@gmail.com Mr or Mrs. 500 Group: General Forum Members Points: 525 Visits: 627 Thanks for the clarifications, Al. I appreciate the finer points and I am sure this will be helpful to any readers as well.Mark piet_dj SSC-Enthusiastic Group: General Forum Members Points: 194 Visits: 218 Do you have any useful info on calculating multiple linear regression with T-SQL? SwePeso SSC-Insane Group: General Forum Members Points: 22231 Visits: 3433 piet_dj (2/10/2010)Do you have any useful info on calculating multiple linear regression with T-SQL?Here http://developerworkshop.net/software.htmlIt also handles logarithmic, exponential and power regression. N 56°04'39.16"E 12°55'05.25" ltaylor 73774 Forum Newbie Group: General Forum Members Points: 3 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. mark.wojciechowicz@gmail.com Mr or Mrs. 500 Group: General Forum Members Points: 525 Visits: 627 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 Tom Thomson SSC Guru Group: General Forum Members Points: 50780 Visits: 13159 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 Edward Boyle-478467 Mr or Mrs. 500 Group: General Forum Members Points: 559 Visits: 1207 Excellent article. I have a lot of forecast and actual time-series data and will check out the methods this weekend. jolifox Grasshopper Group: General Forum Members Points: 18 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. mark.wojciechowicz@gmail.com Mr or Mrs. 500 Group: General Forum Members Points: 525 Visits: 627 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 SwePeso SSC-Insane Group: General Forum Members Points: 22231 Visits: 3433 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"