# 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
Patrick Matthews

membership
Create an account to see this answer
Signing up is free. No credit card required.
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``````
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 ?

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

membership
Create an account to see this answer
Signing up is free. No credit card required.
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``````
Stephan_Schrandt,