linear interpolation

my data is like this:

header | data | key
------
500 | 3.2 | 10
500 | 3.4 | 20
500 | 3.6 | 25
500 | 3.7 | 40
501 | 4.1 | 10
501 | 4.2 | 15
501 | 4.4 | 30
501 | 4.6 | 35

and what I want to do is find the median of "data", but keyed off of "key", so if my desired median is 30, I want to take the two records (data, key) nearest to key = 30, and get the average of "data".
...and do this within each "header" value.

actually, to be precise, I want the linear interpolation, so for header = 500, I want to get the (data, key) pairs of (3.6, 25) and (3.7, 40) and return the interpolated "data" value of 3.6333 (as done here (http://en.wikipedia.org/wiki/Linear_interpolation))

so for the above example the query would produce:

header | interp
------
500 | 3.633
501 | 4.4

possible, or am I crazy?looks like I may have figured it out :)

I created a function to do the interpolation, turned out to be rather easy.|||Got here late. Sorry no one could help you before you sussed it.

Dude - post your solution so others can gain too.|||thank you:beer:|||Got here late. Sorry no one could help you before you sussed it.

Dude - post your solution so others can gain too.

whoops, was going to but totally forgot.

first created an interpolation function:

create function fnInterpolate(@.header int, @.interp float)
returns float
as
begin
declare @.x0 float, @.x1 float, @.y0 float, @.y1 float, @.slope float, @.b float;

select top 1 @.x0 = key, @.y0 = data from the_table where header=@.header and key < @.interp order by key desc;

select top 1 @.x1 = key, @.y1 = data from the_table where header=@.header and key >= @.interp order by key asc;

set @.slope = (@.y1 - @.y0) / (@.x1 - @.x0);
set @.b = @.y1 - (@.slope * @.x1);
return (@.slope * @.interp) + @.b;
end

then did:

select header, fnInterpolate(header) from the_table

sorry it's a bit messy, but the actual usage is a bit more complex so I wanted to simplify. basically I select "header" from the table, and call fnInterpolate on that header with a set of conditions I specify. then fnInterpolate uses those conditions to get the two points are around the interpolation point, computes the interpolation, and returns it.

very well could be a more elegant way to do this though.