Updated: 17 August 2010
Use INTERP_q to calculate the interpolated value of y given x in the array (x, y). INTERP_q uses linear interpolation. INTERP_q is not sensitive to the order of the array (x, y).
Syntax
SELECT [westclintech].[wct].[INTERP_q] (
<@XY_RangeQuery, nvarchar(4000),>
,<@New_x, float,>)
Arguments
@XY_RangeQuery
the select statement, as text, used to determine the known x- and y-values to be used in this function. Data returned from the @XY_RangeQuery select must be of the type float or of a type that implicitly converts to float.
@New_x
the new x-value for which you want trend to calculate the y-value. @New_x is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If the number of known-y data points is not equal to the number of known-x data points, INTERP_q will return an error.
· No GROUP BY is required for this function even though it produces aggregated results.
· For simpler queries use the INTERP function.
· For cubic spline interpolation use the SPLINE_q function.
· If @New_x is less than the smallest value or greater than the largest x-value in the array, an error will be returned.
· If NULL is in the array and used in the interpolation calculation, an error will be returned.
Examples
Interpolation on a table that is in descending order:
create table #xy (
[x] [float] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES(16,65536)
INSERT INTO #xy VALUES(14,16384)
INSERT INTO #xy VALUES(12,4096)
INSERT INTO #xy VALUES(10,1024)
INSERT INTO #xy VALUES(8,256)
INSERT INTO #xy VALUES(6,64)
INSERT INTO #xy VALUES(4,16)
INSERT INTO #xy VALUES(2,4)
INSERT INTO #xy VALUES(0,1)
INSERT INTO #xy VALUES(-2,0.25)
INSERT INTO #xy VALUES(-4,0.0625)
INSERT INTO #xy VALUES(-6,0.015625)
INSERT INTO #xy VALUES(-8,0.00390625)
INSERT INTO #xy VALUES(-10,0.0009765625)
INSERT INTO #xy VALUES(-12,0.000244140625)
INSERT INTO #xy VALUES(-14,0.00006103515625)
INSERT INTO #xy VALUES(-16,0.0000152587890625)
SELECT wct.INTERP_q('Select x, y from #xy', 2.5) as INTERP
This produces the following result
INTERP
----------------------
7
(1 row(s) affected)
Interpolation using dates
create table #xy (
[x] [datetime] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES('09/30/2009',0.00041)
INSERT INTO #xy VALUES('11/30/2009',0.00096)
INSERT INTO #xy VALUES('03/31/2010',0.00198)
INSERT INTO #xy VALUES('09/30/2010',0.00362)
INSERT INTO #xy VALUES('09/30/2011',0.00984)
INSERT INTO #xy VALUES('09/30/2012',0.0155)
INSERT INTO #xy VALUES('09/30/2014',0.02449)
INSERT INTO #xy VALUES('09/30/2019',0.03476)
INSERT INTO #xy VALUES('09/30/2029',0.04234)
SELECT wct.INTERP_q('Select cast(x as float)
,y from #xy'
,cast(cast('08/15/2010' as datetime) as float)
) as INTERP
This produces the following result
INTERP
----------------------
0.00320775956284153
(1 row(s) affected)
Interpolation with invalid data in the array
create table #xy (
[x] [datetime] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES('09/30/2009',0.00041)
INSERT INTO #xy VALUES('11/30/2009',0.00096)
INSERT INTO #xy VALUES('03/31/2010',0.00198)
INSERT INTO #xy VALUES('09/30/2010',NULL)
INSERT INTO #xy VALUES('09/30/2011',0.00984)
INSERT INTO #xy VALUES('09/30/2012',0.0155)
INSERT INTO #xy VALUES('09/30/2014',0.02449)
INSERT INTO #xy VALUES('09/30/2019',0.03476)
INSERT INTO #xy VALUES('09/30/2029',0.04234)
SELECT wct.INTERP_q('Select cast(x as float)
,y from #xy'
,cast(cast('08/15/2010' as datetime) as float)
) as INTERP
This produces the following result
INTERP
----------------------
Msg 6522, Level 16, State 1, Line 15
A .NET Framework error occurred during execution of user-defined routine or aggregate "INTERP_q":
XLeratorDB_math.Core.Exceptions+Function_Parameter_Exception:
[Product version 1.2 Build: 0927.121]
Invalid 'Known_y' parameter value passed to function 'INTERP'
*** Error information:
Known_y interpolation point is NULL.
Known_x(0) 40084 Known_y(0) 0.00041
Known_x(1) 40145 Known_y(1) 0.00096
Known_x(2) 40266 Known_y(2) 0.00198
Known_x(3) 40449 Known_y(3) [NULL] *
Known_x(4) 40814 Known_y(4) 0.00984
Known_x(5) 41180 Known_y(5) 0.0155
Known_x(6) 41910 Known_y(6) 0.02449
Known_x(7) 43736 Known_y(7) 0.03476
Known_x(8) 47389 Known_y(8) 0.04234
See Also