January 29, 2009 at 7:22 am
Does anyone know if there are there any algorithms/ formulas that can be applied within SQL/excel to alert the user if the trend changes significantly?
I have all the graphs created (data pulled from an SQL query into Excel and have created trendlines-moving averages within excel)
but im looking for a way to predict these changes in advance...
any ideas?
January 30, 2009 at 6:55 pm
Are you looking to find if a current value is much different from the predicted?
Since you're using Excel the formula and R-Squared value can be displayed for the trendline. Or, you can generate the formulas for the trendline yourself. Then, based on that formula plug in the future values and when then compare that to the actual.
As far as generating an alert on the difference between the actual vs. forecast, a user settable counter (sp_user_counter) could be used with an SQL Agent alert. The alert would use the SQL Server performance condition alert for the type and select SQLSERVER:User Settable for the object.
Using a linear trendline as an example.
CREATE TABLE Table1 (x int, y float)
INSERT INTO table1(x,y) VALUES(1,10)
INSERT INTO table1(x,y) VALUES(2,12)
INSERT INTO table1(x,y) VALUES(3,8)
INSERT INTO table1(x,y) VALUES(4,14)
INSERT INTO table1(x,y) VALUES(5,16)
INSERT INTO table1(x,y) VALUES(6,14)
INSERT INTO table1(x,y) VALUES(7,18)
INSERT INTO table1(x,y) VALUES(8,20)
INSERT INTO table1(x,y) VALUES(9,20)
INSERT INTO table1(x,y) VALUES(10,20)
-- y = mx + b
DECLARE @m FLOAT
DECLARE @b-2 FLOAT
SELECT @m = (COUNT(x) * SUM(x*y) - SUM(x) * SUM(y)) / (COUNT(x)*SUM(POWER(x,2)) - POWER(SUM(x),2)) FROM Table1
SELECT @b-2 = (SUM(y) - @m * SUM(x)) / COUNT(x) FROM Table1
-- left out R-Squared but you'd want calculate this too
-- to find a measure of the trendlines reliability.
-- Assuming x represents a day, find predicted value for day 30.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply