 # SQL Server polynomial interpolation

POLYINTERP

Updated: 30 April 2011

Use POLYINTERP to calculate the interpolated value of y given x in the array (x, y). POLYINTERP uses Neville’s method.
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 linear interpolation use the INTERP function.
·         For cubic spline interpolation use the SPLINE function.
·         If an x-y pair contains a NULL, then the pair is not used in the interpolation calculation.
·         POLYTINTERP will accept values for @new_x that are less than the minimum @known_x or greater than the maximum @known_x.
·         @new_x must remain invariant for the GROUP.
·         Available in XLeratorDB / math 2008 only
Examples
Given the following x- and y-values, we want to calculate the y-value for x = 0.75
SET NOCOUNT ON

SELECT wct.POLYINTERP(x,y,0.75) as POLYINTERP
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.
POLYINTERP
----------------------
0.681679954101562

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.POLYINTERP(cast(x as float), y, cast(Cast('2012-Oct-31' as datetime) as float)) as POLYINTERP
from #a

DROP TABLE #a

This produces the following result.
POLYINTERP
----------------------
0.00431302766039499

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.POLYINTERP(
westclintech.wct.YEARFRAC('2011-Apr-30',x,0)
,y
,westclintech.wct.YEARFRAC('2011-Apr-30','2012-Oct-31',0)
) as POLYINTERP
FROM #a
This produces the following result.
POLYINTERP
----------------------
0.00430301258712666

In this example, we provide a @new_x value that is less than the minimum @known_x value.
SET NOCOUNT ON

SELECT wct.POLYINTERP(x, y, -3) as POLYINTERP
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.
POLYINTERP
----------------------
-3.5

In this example, we provide a @new_x value that is greater than the maximum @known_x value.
SET NOCOUNT ON

SELECT wct.POLYINTERP(x, y, 4) as POLYINTERP
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.
POLYINTERP
----------------------
7

In this example, one of the @known_y values is NULL.
SET NOCOUNT ON

SELECT wct.POLYINTERP(x, y, 1.5) as POLYINTERP
FROM (VALUES
(-2,-2),
(-1,-0.5),
(0,1),
(1,NULL),
(2,4),
(3,5.5)
) n(x, y)
This produces the following result.
POLYINTERP
----------------------
3.25

In this example, one of the @known_x values is NULL.
SET NOCOUNT ON

SELECT wct.POLYINTERP(x, y, 1.5) as POLYINTERP
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.
POLYINTERP
----------------------

3.25

### Support  Copyright 2008-2021 Westclintech LLC         Privacy Policy        Terms of Service