sql guy123 (st*****@hotmail.com) writes:

This is a real challenge. I hope someone is smart enough to know how

to do this.

I have a table

TABLE1

[Column 1- 2001]

[Column 2- 2002]

[Column 3- 2003]

[Column 4 - 2004]

[Column 5 - 2005]

[Column 6 - 2006]

[Column 7 - Slope]

[2001][2002][2003][2004][2005][2006] [Slope]

[1] [2] [3] [4] [5] [6] [1]

[1.2] [.9] [4] [5] [5.4] [6.2] [?]

Slope is defined as "M" in the equation y=mx+b

I need a way a finding the linear equation that best fits the points so

I can have SQL calculate the slope.

Are there any smart people around that would know how to do this?

Smart? Either you did learn linear regression in school or you didn't.

I will have to admit that although I taught mathematical statistics as a

student, that I've forgotten the forumulas. But I looked them up in my

statisticcs book:

m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))

where Xi and Yi are the individual values for the X and Y variables.

For the computation in SQL you can of course not use SUM and AVG since

your table is turned the wrong way. The years should have been columns

instead.

--

Erland Sommarskog, SQL Server MVP,

es****@sommarskog.se
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinf...ons/books.mspx