 # SQL Server bi-linear interpolation function

BILINEARINTERP

Updated: 30 September 2013

Use the scalar BILINEARINTERP function to interpolate on a regular 2-dimensional grid. BILINEARINTERP accepts the 2-dimensional grid, a new x-value, and a new y-value as input, where the x-values are stored in the first column of data and the y-values are stored in the first row of data and the data are contained in the table.

The bilinear interpolation calculation can be thought of as a series of linear interpolation calculations using the closest combination of x- and y-values on the 2-dimensional grid. For example, let's look at the following table which shows the number of days in the future as the x-values, the strike price of an option as the y-values, and which contains the volatility associated with the date and the strike price.

 70 80 90 100 110 120 130 30 0.22 0.2 0.18 0.16 0.175 0.19 0.205 90 0.23 0.21 0.19 0.17 0.185 0.2 0.215 182 0.26 0.24 0.22 0.2 0.215 0.23 0.245 270 0.25 0.23 0.21 0.19 0.205 0.22 0.235 365 0.245 0.225 0.205 0.185 0.2 0.215 0.23

If we want to interpolate the volatility for 63 days with a strike price of 87, we can quickly see that the surrounding points would look like this.

 80 90 30 0.2 0.18 90 0.21 0.19

It is then a very straightforward to see that the desired result can be thought of as three linear interpolation calculations, as demonstrated by the following SQL.

SELECT wct.INTERP(x,y,87) as BiLinear
FROM (
SELECT 80 as x
,wct.INTERP(x,y,63) as y
FROM (VALUES (30,.20),(90,0.21))n(x,y)
UNION ALL
SELECT 90
,wct.INTERP(x,y,63)
FROM (VALUES (30,.18),(90,0.19))n(x,y)
)i

Producing the result of 0.1915
Syntax
SELECT [wct].[BILINEARINTERP](
<@Grid, nvarchar(max),>
,<@New_x, float,>
,<@New_y, float,>)
Arguments
@Grid
a T-SQL statement, as text, which when executed returns a resultant table containing the x-values in the first column, the y-values in the first row and the values (z) to be interpolated. In other words the table should look something like this:

 NULL y1 y2 y3 … yn x1 zx1y1 zx1y2 zx1y3 … zx1yn x2 zx2y1 zx2y2 zx2y3 … zx2yn x3 zx3y1 zx3y2 zx3y3 … zx3yn … … … … … xm zxmy1 zxmy2 zxmy3 … zxmyn

All values in the resultant table must be of a type float or of a type the implicitly converts to float.
@New_x
the new x-value. @New_x must be a type float or of a type that implicitly converts to float.
@New_y
the new y-value. @New_y must be a type float or of a type that implicitly converts to float.
Return Types
float
Remarks
·         If @New_x < MIN(X) or @New_x > MAX(X) then a NULL will be returned.
·         If @New_y < MIN(Y) or @New_y > MAX(y) then a NULL will be returned.
·         If the @New_x and @New_y are bound by a grid containing a NULL then a NULL will be returned.
Examples
SELECT wct.BILINEARINTERP('SELECT *
FROM (VALUES
(NULL,-45,-44,-43,-42,-41),
(44,67,17,67,7,66),
(45,53,65,34,3,90),
(46,25,40,21,29,24),
(47,29,69,17,63,51),
(48,71,90,90,57,54),
(49,92,15,71,0,10),
(50,33,73,21,19,99)
)n(x,y1,y2,y3,y4,y5)'
,47.25
,-43.5
) as BILINEARINTERP

This produces the following result.
BILINEARINTERP
----------------------
54.75

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