1. Registered User
Join Date
Oct 2006
Posts
15

my data is like this:

Code:
```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)

so for the above example the query would produce:

Code:
```header | interp
-----------------
500    | 3.633
501    | 4.4```
possible, or am I crazy?

2. Registered User
Join Date
Oct 2006
Posts
15
looks like I may have figured it out

I created a function to do the interpolation, turned out to be rather easy.

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Got here late. Sorry no one could help you before you sussed it.

Dude - post your solution so others can gain too.

4. Registered User
Join Date
Jun 2007
Posts
1
thank you

5. Registered User
Join Date
Oct 2006
Posts
15
Originally Posted by pootle flump
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:
PHP Code:
``` 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:

PHP Code:
``` 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•