INTERCEPT
Updated: 15 January 2011
Use the aggregate INTERCEPT function to calculate the point at which a line will intersect the y-axis by using existing x-values and y-values. The y-intercept is the value of the point which intersects the line at x = 0. In linear equations that are in the slope intercept form of y = mx + b, the value of b is the y-intercept. The equation for intercept is:
Syntax
SELECT [wctStatistics].[wct].[INTERCEPT] (
,<@fmtINTERCEPT, nvarchar(4000),>)
The syntax has changed as of release 1.07 and the function has changed from a scalar to an aggregate. Please make the appropriate changes when you upgrade to 1.07.
Arguments
@fmtINTERCEPT
An nvarchar string formatted by the fmtINTERCEPT function containing the known-y and known-x values to be used by the INTERCEPT calculation.
Return Types
float
Remarks
· INTERCEPT is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
· INTERCEPT is a one-pass solution. For a two-pass solution, use the INTERCEPT_q function.
Examples
In this example, we calculate the intercept for a single set of x- and y-values
SELECT wct.INTERCEPT(wct.fmtINTERCEPT(y, x)) as INTERCEPT
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
INTERCEPT
----------------------
0.972553699284009
(1 row(s) affected)
In this example, we will populate some temporary table with some historical financial information and then calculate the intercept. 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',2008,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',2008,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',2008,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',2008,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',2008,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)
In this example, we will calculate the intercept of the operating income (OPINC) against the revenue (REV)
SELECT SYM
,wct.INTERCEPT(wct.fmtINTERCEPT(OPINC,REV)) as INTERCEPT
FROM #c
GROUP BY SYM
This produces the following result.
SYM INTERCEPT
----- ----------------------
GOOG -533.437373986774
MSFT -2828.42743430843
ORCL -474.461309808912
SAP 1510.83759005268
YHOO 102.901524359151
(5 row(s) affected)
Let’s say we wanted to perform the same analysis as above, but we only want to return the results where the slope is positive.
SELECT SYM
,wct.INTERCEPT(wct.fmtINTERCEPT(OPINC,REV)) as INTERCEPT
FROM #c
GROUP BY SYM
HAVING wct.INTERCEPT(wct.fmtINTERCEPT(OPINC,REV)) > 0
This produces the following result.
SYM INTERCEPT
----- ----------------------
SAP 1510.83759005268
YHOO 102.901524359151
(2 row(s) affected)