Stephan_Schrandt

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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 ?

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 ?

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

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.

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.

ASKER

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

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

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,

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

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

Open in new window