SQL Server BILINEARINTERP Function

Updated 2024-02-13 20:43:07.520000

Description

Use the scalar function BILINEARINTERP 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.

column 1	column 2	column 3	column 4	column 5	column 6	column 7	column 8
	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.

column 1	column 2	column 3
	80	90
30	0.20	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 [westclintech].[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:

column 1	column 2	column 3	column 4	column 5	column 6
NULL	y 1	y 2	y 3	…	y n
x 1	z x1y1	z x1y2	z x1y3	…	z x1yn
x 2	z x2y1	z x2y2	z x2y3	…	z x2yn
x 3	z x3y1	z x3y2	z x3y3	…	z x3yn
…	…	…	…	…
x m	z xmy1	z xmy2	z xmy3	…	z xmyn

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 Type

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