Hi ,
In Excel, there is function -- TREND . I'm wondering how can we achieve this
in SQL Server.
Function will do something like this.
TREND
Returns values along a linear trend. Fits a straight line (using the method
of least squares) to the arrays known_y's and known_x's. Returns the
y-values along that line for the array of new_x's that you specify.
Syntax
TREND(known_y's,known_x's,new_x's,const)
Known_y's is the set of y-values you already know in the relationship y = mx
+ b.
If the array known_y's is in a single column, then each column of known_x's
is interpreted as a separate variable.
If the array known_y's is in a single row, then each row of known_x's is
interpreted as a separate variable.
Known_x's is an optional set of x-values that you may already know in the
relationship y = mx + b.
The array known_x's can include one or more sets of variables. If only one
variable is used, known_y's and known_x's can be ranges of any shape, as
long as they have equal dimensions. If more than one variable is used,
known_y's must be a vector (that is, a range with a height of one row or a
width of one column).
If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is
the same size as known_y's.
New_x's are new x-values for which you want TREND to return corresponding
y-values.
New_x's must include a column (or row) for each independent variable, just
as known_x's does. So, if known_y's is in a single column, known_x's and
new_x's must have the same number of columns. If known_y's is in a single
row, known_x's and new_x's must have the same number of rows.
If you omit new_x's, it is assumed to be the same as known_x's.
If you omit both known_x's and new_x's, they are assumed to be the array
{1,2,3,...} that is the same size as known_y's.
Const is a logical value specifying whether to force the constant b to equal
0.
If const is TRUE or omitted, b is calculated normally.
If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted
so that y = mx.
I can replicate the function when the const value is TRUE by using the REGR_SLOPE and REGR_INTERCEPT function. However I am not able to replicate for const = FALSE. It would be of great help if anyone could help on this.
Only the known y's are available. The known x's are {1,2,3....} and the new x is always 1. The const is FALSE. Following is a sample output
The value 0.17272 is calculated by the trend function =TREND(0.50:0.18,,,FALSE).
Thank you.