INTERP
Updated: 30 April 2011
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 INTERP function
Use INTERP to calculate the straight-line interpolated value of y given x in the array (x, y). INTERP is not sensitive to the order of the array (x, y).Syntax
Arguments
@known_x
the x-values to be used in the interpolation calculation. @known_x must be of the type float or of a type that implicitly converts to float.
@known_y
the y-values to be used in the interpolation calculation. @known_y 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 INTERP to calculate the y-value.
Return Types
float
Remarks
· For cubic spline interpolation use the SPLINE function.
· For polynomial interpolation, use the POLYINTERP function.
· If @New_x is less than the smallest x-value or greater than the largest x-value in the array, an error will be returned.
· If an x-y pair contains a NULL, then the pair is not used in the interpolation calculation.
· If @new_x = @known_x then @known_y is returned.
· @new_x must remain invariant for the GROUP.
Examples
Given the following x- and y-values, we want to calculate the y-value for x = 0.75
SET NOCOUNT ON
SELECT wct.INTERP(x,y,0.75) as INTERP
FROM (VALUES
(0.5,0.479426),
(1,0.841471),
(1.5,0.997495),
(2,0.909297),
(2.5,0.598472),
(3,0.14112),
(3.5,-0.350783),
(4,-0.756802)
) n(x,y)
This produces the following result.
INTERP
----------------------
0.6604485
In this example, we will interpolate using dates.
SET NOCOUNT ON
SELECT cast(x as datetime) as x,y
INTO #a
FROM (VALUES
('2012-Apr-30',0.0028),
('2013-Apr-30',0.0056),
('2014-Apr-30',0.0085),
('2016-Apr-30',0.0164),
('2018-Apr-30',0.0235),
('2021-Apr-30',0.0299),
('2031-Apr-30',0.0382),
('2041-Apr-30',0.0406)
) m(x, y)
SELECT wct.INTERP(cast(x as float), y, cast(Cast('2012-Oct-31' as datetime) as float)) as INTERP
from #a
DROP TABLE #a
This produces the following result.
INTERP
----------------------
0.00421150684931507
We could have used the YEARFRAC function from XLeratorDB/financial, which makes the SQL somewhat simpler, but also permits interpolation using different interest bases. In this example, we will use YEARFRAC, using the 3-part function identifier because the YEARFRAC function is contained in a different database, to interpolate using the BOND interest basis.SET NOCOUNT ON
SELECT cast(x as datetime) as x,y
INTO #a
FROM (VALUES
('2012-Apr-30',0.0028),
('2013-Apr-30',0.0056),
('2014-Apr-30',0.0085),
('2016-Apr-30',0.0164),
('2018-Apr-30',0.0235),
('2021-Apr-30',0.0299),
('2031-Apr-30',0.0382),
('2041-Apr-30',0.0406)
) m(x, y)
SELECT wct.INTERP(
westclintech.wct.YEARFRAC('2011-Apr-30',x,0)
,y
,westclintech.wct.YEARFRAC('2011-Apr-30','2012-Oct-31',0)
) as INTERP
from #a
DROP TABLE #a
This produces the following result.
INTERP
----------------------
0.0042
In this example, we provide a @new_x value that is less than the minimum @known_x value.
SET NOCOUNT ON
SELECT wct.INTERP(x, y, -3) as INTERP
FROM (VALUES
(-2,-2),
(-1,-0.5),
(0,1),
(1,2.5),
(2,4),
(3,5.5)
) n(x, y)
This produces the following result.
INTERP
----------------------
NULL
In this example, we provide a @new_x value that is greater than the maximum @known_x value.
SET NOCOUNT ON
SELECT wct.INTERP(x, y, 4) as INTERP
FROM (VALUES
(-2,-2),
(-1,-0.5),
(0,1),
(1,2.5),
(2,4),
(3,5.5)
) n(x, y)
This produces the following result.
INTERP
----------------------
NULL
In this example, one of the @known_y values is NULL.
SET NOCOUNT ON
SELECT wct.INTERP(x, y, 1.5) as INTERP
FROM (VALUES
(-2,-2),
(-1,-0.5),
(0,1),
(1,NULL),
(2,4),
(3,5.5)
) n(x, y)
This produces the following result.
INTERP
----------------------
3.25
In this example, one the @known_x values is NULL.
SET NOCOUNT ON
SELECT wct.INTERP(x, y, 1.5) as INTERP
FROM (VALUES
(-2,-2),
(-1,-0.5),
(0,1),
(1,2.5),
(NULL,4),
(3,5.5)
) n(x, y)
This produces the following result.
INTERP
----------------------
3.25