Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server STEYX function


STEYX

Updated: 24 September 2010

Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the STEYX function


Use the multi-input aggregate STEYX function to return the standard error of the predicted y-value for each x in the regression. The equation for STEYX is:

STEYX function for SQL Server 
 
Syntax
SELECT [wctStatistics].[wct].[SLOPE] (
 ,<@Known_y, float,>
 ,<@Known_x, float,>)
Arguments
 
@Known_y
the y-values to be used in the STEYX calculation. @Known_y is an expression of type float or of a type that can be implicitly converted to float.
@Known_x
the x-values to be used in the STEYX calculation. @Known_x is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         STEYX is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
Examples
In this example, we calculate the standard error for a single set of x- and y-values
SELECT wct.STEYX(y, x) as STEYX
FROM (
      SELECT 0.75, 1 UNION ALL
      SELECT 2.5, 2 UNION ALL
      SELECT 6.75, 3 UNION ALL
      SELECT 10, 4
      ) n(x,y)
 
This produces the following result
STEYX
----------------------
0.2368246584594
 
(1 row(s) affected)
In this example, we will populate some temporary table with some historical financial information and then calculate the standard error. First, create the table and put some data in it:
CREATE TABLE #c(
      SYM         NVARCHAR(5),
      YE          BIGINT,
      REV         FLOAT,
      GPROF       FLOAT,
      OPINC       FLOAT,
      NETINC      FLOAT
      )
     
INSERT INTO #c VALUES('YHOO',2009,6460.32,3588.57,386.69,597.99)
INSERT INTO #c VALUES('YHOO',20,72.5,4185.14,12.96,418.92)
INSERT INTO #c VALUES('YHOO',2007,6969.27,4130.52,695.41,639.16)
INSERT INTO #c VALUES('YHOO',2006,6425.68,3749.96,940.97,751.39)
INSERT INTO #c VALUES('YHOO',2005,5257.67,3161.47,1107.73,1896.23)
INSERT INTO #c VALUES('GOOG',2009,23650.56,14806.45,8312.19,6520.45)
INSERT INTO #c VALUES('GOOG',20,21795.55,13174.04,5537.21,4226.86)
INSERT INTO #c VALUES('GOOG',2007,16593.99,9944.9,54.44,4203.72)
INSERT INTO #c VALUES('GOOG',2006,10604.92,6379.89,3550,3077.45)
INSERT INTO #c VALUES('GOOG',2005,6138.56,3561.47,2017.28,1465.4)
INSERT INTO #c VALUES('MSFT',2010,62484,509,24167,18760)
INSERT INTO #c VALUES('MSFT',2009,58437,46282,21225,14569)
INSERT INTO #c VALUES('MSFT',20,60420,48822,22271,17681)
INSERT INTO #c VALUES('MSFT',2007,51122,40429,18438,14065)
INSERT INTO #c VALUES('MSFT',2006,44282,36632,16064,12599)
INSERT INTO #c VALUES('ORCL',2010,26820,21056,9062,6135)
INSERT INTO #c VALUES('ORCL',2009,23252,18458,8321,5593)
INSERT INTO #c VALUES('ORCL',20,22430,17449,7844,5521)
INSERT INTO #c VALUES('ORCL',2007,17996,13805,5974,4274)
INSERT INTO #c VALUES('ORCL',2006,14380,11145,4736,3381)
INSERT INTO #c VALUES('SAP',2009,10672,6980,2588,1748)
INSERT INTO #c VALUES('SAP',20,11575,7370,2701,1847)
INSERT INTO #c VALUES('SAP',2007,10256,6631,2698,1906)
INSERT INTO #c VALUES('SAP',2006,9393,6064,2578,1871)
INSERT INTO #c VALUES('SAP',2005,8509,5460,2337,1496)
Now, calculate the standard error of the revenue (REV) againt the year (YE) for each company (SYM)
SELECT #c.SYM
,wct.STEYX(REV,YE) as STEYX
FROM #c
GROUP BY SYM
This produces the following result.
SYM   STEYX
----- ----------------------
GOOG 1284.7880744898
MSFT 3433.65363716261
ORCL 1002.72854418997
SAP  663.59214381526
YHOO 645.466427850053
 
(5 row(s) affected)
In this example, we will calculate the standard error of the operating income (OPINC) against the revenue (REV)
SELECT #c.SYM
,wct.STEYX(OPINC,REV) as STEYX
FROM #c
GROUP BY SYM
 
This produces the following result.
SYM   STEYX
----- ----------------------
GOOG 915.059820560669
MSFT 580.20345862298
ORCL 253.04217864277
SAP  91.5324292146263
YHOO 324.424674667298
 

(5 row(s) affected)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service