INTERP2
Updated: 30 September 2013
Use the aggregate function INTERP2 to interpolate on a 2dimensional grid. INTERP2 expects the grid to be represented in 3^{rd} normal form. INTERP2 is not sensitive to the order of the input.
The 2dimensional or bilinear interpolation calculation can be thought of as a series of linear interpolation calculations using the closest combination of x and yvalues on the 2dimensional grid. For example, let's look at the following table which shows the number of days in the future as the xvalues, the strike price of an option as the yvalues, and which contains the volatility associated with the date and the strike price.
SELECT x,y,z
INTO #v
FROM (VALUES
(30,70,0.22),
(30,80,0.2),
(30,90,0.18),
(30,100,0.16),
(30,110,0.175),
(30,120,0.19),
(30,130,0.205),
(90,70,0.23),
(90,80,0.21),
(90,90,0.19),
(90,100,0.17),
(90,110,0.185),
(90,120,0.2),
(90,130,0.215),
(182,70,0.26),
(182,80,0.24),
(182,90,0.22),
(182,100,0.2),
(182,110,0.215),
(182,120,0.23),
(182,130,0.245),
(270,70,0.25),
(270,80,0.23),
(270,90,0.21),
(270,100,0.19),
(270,110,0.205),
(270,120,0.22),
(270,130,0.235),
(365,70,0.245),
(365,80,0.225),
(365,90,0.205),
(365,100,0.185),
(365,110,0.2),
(365,120,0.215),
(365,130,0.23)
)n(x,y,z)
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(y, z, 87) as INTERP2
FROM (
SELECT y, wct.INTERP(x,z,63) as z
FROM #v
WHERE y = (SELECT MAX(y) FROM #v WHERE y <= 87)
GROUP BY y
UNION
SELECT y, wct.INTERP(x,z,63)
FROM #v
WHERE y = (SELECT MIN(y) FROM #v WHERE y > 87)
GROUP BY y
)m
Producing the result of 0.1915
Syntax
Arguments
@X
The value of the coordinate along the xaxis. @X must be of a type float or of a type that implicitly converts to float.
@Y
The value of the coordinate along the yaxis. @Y must be of a type float or of a type that implicitly converts to float.
@Z
The value contain at (@X,@Y). @Z must be of a type float or of a type that implicitly converts to float.
@New_x
the new xvalue used by INTERP2 to calculate the interpolated zvalue. @New_x must be of a type float or of a type that implicitly converts to float.
@New_y
the new yvalue used by INTERP2 to calculate the interpolated zvalue. @New_y must be of a type float or of a type that implicitly converts to float.
Return Types
float
Remarks
· For data not in 3^{rd} normal form, consider using the BILINEARINTERP function.
· If @New_x is less than the smallest xvalue or greater than the largest xvalue in the array, an error will be returned.
· 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.
· @New_x must remain invariant for the GROUP.
· @New_y must remain invariant for the GROUP.
Examples
SELECT wct.INTERP2(x,y,z,63,87) as INTERP2
FROM (VALUES
(30,70,0.22),
(30,80,0.2),
(30,90,0.18),
(30,100,0.16),
(30,110,0.175),
(30,120,0.19),
(30,130,0.205),
(90,70,0.23),
(90,80,0.21),
(90,90,0.19),
(90,100,0.17),
(90,110,0.185),
(90,120,0.2),
(90,130,0.215),
(182,70,0.26),
(182,80,0.24),
(182,90,0.22),
(182,100,0.2),
(182,110,0.215),
(182,120,0.23),
(182,130,0.245),
(270,70,0.25),
(270,80,0.23),
(270,90,0.21),
(270,100,0.19),
(270,110,0.205),
(270,120,0.22),
(270,130,0.235),
(365,70,0.245),
(365,80,0.225),
(365,90,0.205),
(365,100,0.185),
(365,110,0.2),
(365,120,0.215),
(365,130,0.23)
)n(x,y,z)
This produces the following result.
INTERP2

0.1915