I have been asked to do forecasting in SQL as previously it has been done in Excel using the forecast.ets function in Excel.
What I need to do is forecast the members count in the coming months based on historical member numbers.
Is there a SQL function that can do this?
Not in the Transact-SQL language for SQL Server.
Since SQL Server 2016, you can run R statistical code within SQL Server, and SQL 2017 added Python as well. It falls under their Machine Learning Services:
If you search for "Python forecasting functions" you'll find some libraries that you could use directly in SQL Server. Whether they have the exact forecasting function I don't know, I couldn't find documentation on the algorithm used in that Excel function.
There are also analysis functions available in DAX (for Tabular models) and MDX (for multidimensional) in the Analysis Services feature. Both would require additional data modeling/design in order to work well however.
Lastly (and I mean you want to exhaust every other option...including continuing to do this in Excel), you *might* be able to create a CLR function in .Net to perform the forecasting function, and register that function in your SQL Server. There are security concerns about enabling CLR support in SQL Server, and the code needs to be written in a .Net language like C#.
I hesitate to suggest this because, assuming it's difficult or impossible to reproduce the function, you might be inclined to use .Net to create an Excel instance that uses that forecast function, and if that even works in SQLCLR, it would most likely cause significant performance and other problems.
