Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server 2005 linear interpolation function


 
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
·         SPLINE_q
·         INTERP

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service