Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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.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 [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
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service