 # SQL Server linear interpolation function

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.

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

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