0

I'm stuck on this one...

...we're using linear regression for some trending and forecasting and I'm having to query data, create a dataset, then paste into excel and apply a linest function to my data. Since the data requirements have changed daily, this has become a very cumbersome thing to whip together. I'd want SQL Server to take care of that processing as this will be an automated forecast that I do not want to touch after I hand it over to an end user. When they refresh the data, I want it to refresh the linest function.

Here's some sample data

The [JanTrend] is a logarithmic trend in Excel that takes the trend of the Jan-12, Jan-13, and Jan-14 fields and calculates.

Here's that function in Excel

=LINEST([Jan-12]:[Jan-14]^{1})

The Forecasted field is basically [Jan-14] + [JanTrend]. 

StockCode   Jan-12  Jan-13  Jan-14  JanTrend   Forecasted

300168           2       3      11        5           16
300169           1       4       3        1            4

The JanTrend field is where my linest function is located in my excel spreadsheet.

I want to convert the above function to T-SQL or in an SSRS report. How can I achieve this?

EDIT: I'm trying to calculate a logarithmic trend. I made some changes to my sample data to makes things more clear.

jdids
  • 561
  • 1
  • 7
  • 22
  • If you explain how the `Linest` calculates it's value it will be easy to convert it into the `sql` or ``expression` – Mahesh Jan 23 '15 at 06:02
  • It takes the Jan-12, Jan-13, and Jan-14 values and tries to calculate a logarithmic trendline. My apologies, I should have noted that. – jdids Jan 23 '15 at 18:21

1 Answers1

0

the linest excel function is just linear regression. it's (still) not available in sql server, but you will find a lot of examples of UDF's or queries implementing it. just google for "sqlserver udf linear regression" or refer to this previous question. Are there any Linear Regression Function in SQL Server?

udf's are generally slow, so you might want to go with the solution in the third post in this forum. http://www.sqlservercentral.com/Forums/Topic710626-338-1.aspx

Community
  • 1
  • 1
busytools
  • 361
  • 1
  • 6