August 15, 2018 at 8:01 pm
Hi r987d,
If you download the link at the end of the article, it has the data used for the example and all the queries step-by step.
m
May 9, 2019 at 11:38 am
Hi,
Great article, I am totally unable to find the download?
Thanks in advance
May 13, 2019 at 5:42 pm
Yep, there doesn't seem to be any download link...there used to be, but not anymore that I can find.
July 14, 2019 at 10:31 pm
Yep, there doesn't seem to be any download link...there used to be, but not anymore that I can find.
There is a download link now, but it seems to be badly typed from the point of view of something referenced on a webpage - it appears to want to be opened in SSMS but provdes an imaginary username (based on the name of your computer and the name of the person running the browser) which people may consider as easily stolen data and therefor that username won't have the privilege of running SSMS or of connecting SSMS to any DBMS, especially running it with a blank password, which is what the download tries to do. If you open SSMS using the same username as you use to run the browser and connect to an SQL Server instance before attempting the download it, it downloads just fine. Personally, I would prefer to see such files presented as text documents, not as code to be displayed by an interpreter.
I'm no expert on putting SMSS-fodder into an attachment for access over the web, so I may have misinterpreted what's going on - but on my current system if I want to see that file I need to have SSMS running and connected to a DBMS before I attempt the download, and i must do the download with the user which has that open SSMS connection to SQL Server. That may be one of the bugs in the version of Windows 10 that I'm running, rather that a fault on the attachment to the forecasting paper. I'm fed up with having to run recovery and repair tools after just about every windows or SQL Server update is installed to get my system working somewhere near properly again, and having to find someone who can make it work on the rare occassions when it's beyond me, and I'm pretty sure that the tools don't fix all the problems and neither does clearing out corrupted updates and then running the proper ones, but I allow all released updates to happen if windows update decides it wants them and then I try to put up with the consequences.
Tom
May 21, 2020 at 5:41 pm
Mark,
For a beginner this article is fantastic! Thank you so much in putting in the work for the layperson.
I modified the code with the idea of using the DATEADD approach, however, my code is generating to many results I believe. Would you be so kind at to review the following to point out my error? Thanks.
----------****************************************************************************
----------Example for Peforming Linear Regression with SQL
---------- Author: Mark Wojciechowicz
---------- Date: 12/29/2009
----------****************************************************************************
--*****************************************************************************
--
--Step 1 - Create a table variable and insert test data.
--Test data is stripped of promotions and aggregated to a monthly level.
--Then update smoothed_quantity with a central moving average
--
--*****************************************************************************
-- Create Table Variable to hold results
DROP TABLE tmp.ForecastTable
CREATE TABLE tmp.ForecastTable(
ForecastKey INT IDENTITY(1,1) NOT NULL
,PostDate DATE NULL
,LeadType VARCHAR(50) NULL
,Amount BIGINT NULL
,Smoothed_Quantity BIGINT
,Trend numeric(38,17)
,Seasonality numeric(38,17)
,Forward_Trend Numeric(38,17)
,Forecast Numeric(38,17)
)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('4/1/2019','CANDY',12804)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('5/1/2019','CANDY',2322)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('6/1/2019','CANDY',5643)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('7/1/2019','CANDY',9039)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('8/1/2019','CANDY',4245)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('9/1/2019','CANDY',2851)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('10/1/2019','CANDY',4637)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('11/1/2019','CANDY',4033)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('12/1/2019','CANDY',4468)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('1/1/2020','CANDY',153)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('2/1/2020','CANDY',5122)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('3/1/2020','CANDY',6656)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('4/1/2020','CANDY',4944)
Insert into tmp.ForecastTable (PostDate, LeadType, Amount) Values ('5/1/2020','CANDY',2599)
-- Create table to store calculations by Item
----- Update smoothed_Quantity with Central Moving Average
UPDATE tmp.ForecastTable
SET Smoothed_Quantity = MovAvg.Smoothed_Quantity
FROM(
SELECT
a.ForecastKey as FKey,
a.LeadType as LType,
ROUND(AVG(Cast(b.Amount as numeric(14,1))),0) Smoothed_Quantity
FROM
tmp.ForecastTable a
INNER JOIN
tmp.ForecastTable b
ON
a.LeadType = b.LeadType
AND(a.ForecastKey - b.ForecastKey) BETWEEN -3 AND 3
GROUP BY
a.ForecastKey,
a.LeadType) MovAvg
WHERE LeadType = MovAvg.LType
AND ForecastKey = MovAvg.FKey
--****************************************************************************************
--
--Step 2 - Create a second table variable to hold the trend formula by item.
--This step is performed with an insert and update to make the calculations more clear
--It could just as easily be performed with a single insert.
--Lastly, update the trend for historical data and calculate seasonality
--
--*****************************************************************************************
---- Create table to store calculations by Item
DROP TABLE tmp.Formula
CREATE TABLE tmp.Formula(
LeadType nvarchar(25),
Counts int,
SumX Numeric(14,4),
SumY Numeric(14,4),
SumXY Numeric(14,4),
SumXsqrd Numeric(14,4),
b Numeric(38,17),
a Numeric(38,17))
INSERT INTO tmp.Formula (LeadType, Counts, SumX, SumY, SumXY, SumXsqrd)
(SELECT
LeadType,
COUNT(*),
sum(ForecastKey),
sum(Smoothed_Quantity),
sum(Smoothed_Quantity * ForecastKey),
sum(power(ForecastKey,2))
FROM
tmp.ForecastTable
WHERE
Smoothed_Quantity IS NOT NULL
GROUP BY
LeadType)
-- Calculate B (Slope)
UPDATE tmp.Formula
SET
b = ((tb.counts * tb.sumXY)-(tb.sumX * tb.sumY))/ (tb.Counts * tb.sumXsqrd - power(tb.sumX,2))
FROM
(SELECT LeadType as XLeadType, Counts, SumX, SumY, SumXY, SumXsqrd FROM tmp.Formula) tb
WHERE LeadType = tb.XLeadType
--Calculate A (Y Intercept)
UPDATE
tmp.Formula
SET
a = ((tb2.sumY - tb2.b * tb2.sumX) / tb2.Counts)
FROM
(SELECT LeadType as XLeadType, Counts, SumX, SumY, SumXY, SumXsqrd, b FROM tmp.Formula) tb2
WHERE LeadType = tb2.XLeadType
-- Update Historical Trend and Seasonality
--y = a + bx
--Forecast = Y Intercept + (Slope * ForecastKey)
UPDATE
tmp.ForecastTable
SET
Trend = A + (B * ForecastKey),
Seasonality = CASE WHEN Amount = 0 THEN 1 ELSE Amount /(A + (B * ForecastKey)) END
FROM (SELECT LeadType as XLeadType, Counts, SumX, SumY, SumXY, SumXsqrd, b, a FROM tmp.Formula) TrendUpdate
WHERE LeadType = TrendUpdate.XLeadType
--**********************************************************************************
--
--Step 3 - Insert Trendline and forecast into Forecast table
--
--**********************************************************************************
DECLARE @Loop as int = 0
WHILE @Loop <12
BEGIN
INSERT INTO tmp.ForecastTable (PostDate, LeadType, Forward_Trend, Forecast)
SELECT
DATEADD(m,@loop,a.PostDate) As PostDate
,a.LeadType As LeadType
,MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)) As Forward_Trend
,(MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)))
*
(SELECT Case WHEN AVG(Seasonality) = 0 THEN 1 ELSE AVG(Seasonality) END
FROM tmp.ForecastTable As SeasonalMask
WHERE SeasonalMask.LeadType = a.LeadType
AND SeasonalMask.PostDate = DATEADD(m,@Loop + 1,a.PostDate)
) As Forecast -- Trendline * Avg seasonality
FROM
tmp.ForecastTable a
INNER JOIN
tmp.Formula b
ON
a.LeadType = b.LeadType
GROUP BY
a.PostDate
,a.LeadType
SET @Loop = @Loop + 1
END
-- Review results
SELECT * FROM tmp.ForecastTable Order by LeadType, ForecastKey
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply