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)
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)
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;
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.