Calculate linear trend line in TSQL

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

Select allOpen 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 ?

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

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

Select allOpen 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