USE [AdventureWorksDW]
GO
CREATE PROCEDURE dbo.SalesForecast
@periods as int = 12,
@predictionInterval int = 95
AS
SET NOCOUNT ON;
BEGIN
DECLARE @script AS NVARCHAR (MAX) = N'library(forecast);
InputDataSetToUse <- InputDataSet[-nrow(InputDataSet),]
tseries <- ts(InputDataSetToUse$Sales, start = c(as.numeric(format(min(as.Date(InputDataSetToUse$Months, format="%Y-%m-%d")),"%Y")),as.numeric(format(min(as.Date(InputDataSetToUse$Months, format="%Y-%m-%d")),"%m"))), end = c(as.numeric(format(max(as.Date(InputDataSetToUse$Months, format="%Y-%m-%d")),"%Y")),as.numeric(format(max(as.Date(InputDataSetToUse$Months, format="%Y-%m-%d")),"%m"))), frequency=12)
ForecastResult <- cbind("Predicted",Date=format(seq(max(as.Date(InputDataSet$Months, format="%Y-%m-%d")),length.out=pLength,by="months"), format="%Y-%m-%d"),as.data.frame(forecast(auto.arima(tseries),h=pLength,level=pInterval)))
InputData <- cbind("Actual",format(seq(min(as.Date(InputDataSetToUse$Months, format="%Y-%m-%d")),length.out=nrow(InputDataSet)-1,by="months")), data.frame(InputDataSetToUse$Sales, NA, NA))
colnames(InputData) <-colnames(ForecastResult)
OutputDataSet <- rbind(InputData, ForecastResult)'
EXECUTE sp_execute_external_script @language = N'R', @script = @script,
@input_data_1 =N'SELECT DATEFROMPARTS(YEAR([OrderDate]),MONTH([OrderDate]),1) as [Months], SUM([SalesAmount]) as [Sales]
FROM [AdventureWorksDW].[dbo].[FactInternetSales] group by DATEFROMPARTS(YEAR([OrderDate]),MONTH([OrderDate]),1) order by 1',
@params = N'@pLength int, @pInterval int', @pLength=@periods, @pInterval=@predictionInterval
WITH RESULT SETS (("Indicator" VARCHAR(10),"Date" date NOT NULL, "Sales" real,
"Lower Prediction Interval" REAL,"Upper Prediction Interval" REAL)); 
END;