Link to home
Create AccountLog in
Avatar of Stephan_Schrandt
Stephan_SchrandtFlag for Germany

asked on

Calculate linear trend line in TSQL

Hello,

I need to calculate the trend line for a set of rows in TSQL. Attached a sample file. Please note that the row count can vary. Also I would like to avoid cursors, but I would accept them if there's no other way.

Thank you.
values.xls
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
To actually plot the points, assuming you have a sequential ID column starting at 1...

SELECT (Avg(ID * Value) - Avg(ID) * Avg(Value)) / VarP(ID) AS RegressCoeff,
    Avg(Value) - ((Avg(ID * Value) - Avg(ID) * Avg(Value)) / VarP(ID)) * Avg(ID) AS Intercept
INTO Equation
FROM SomeTable

SELECT s.ID, s.Value, s.ID * e.RegressCoeff + e.Intercept AS LineValue
FROM SomeTable s, Equation e
ORDER BY s.ID

Open in new window

That is a great article by matthewspatrick...

Bit confused by your requirement though. How are you planning on presenting the information ?

Most presentation layer graphics (like Reporting Services) can plot the trend line for you automatically, or, are you after something different ?

If all you need is the trend from point to point, then are you after a simple recursive query ? or do you really want the trend line as above ?
Avatar of Stephan_Schrandt

ASKER

mark_wills:
I have to calculate the accident frequency rate (AFR) as part of a risk plan. This means, there's a question, if the afr trend line is going up or down. So I just need the direction the trend line will go.

matthewspatrick:
I didn't ignore your post but had to do some other thing, I will have a look at it the next two days. Thank you
>>So I just need the direction the trend line will go.

In that case, referring back to my suggestion, the RegressCoeff value is the slope of the line.  If the value is >0, then the trend is increasing, if <0 it is decreasing, and if =0 the trend is flat.
Hello all,
attached the trend line calculated in Excel. Basically the returned value should be -0.2295 which indicates if the trend's going up or down.

matthewspatrick your code returns 0.348 and 11.695. What exactly do these numbers express?

values-w-trendline.xls
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Or going the old fashioned (long hand) way without data type worries :

select
(count(*)*sum([month]*[value])-sum([month])*sum([value]))/(
sqrt(count(*)*sum(square([month]))-square(sum([month])))*
sqrt(count(*)*sum(square([value]))-square(sum([value]))))    -- up to here is correlation
*
((stdev([value]) / stdev([month])))                          -- and this bit makes the slope
from sometable

Open in new window

Stephan_Schrandt,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2799-Simple-Linear-Regression-in-MS-Access.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick