0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

-- generate some grid data values in table #ddd:
CREATE TABLE #ddd (id int,x float,y float, PRIMARY KEY(id,x));
INSERT INTO  #ddd VALUES (1,3,4),(1,4,5),(1,6,3),(1,10,2),
                         (2,1,4),(2,5,6),(2,6,5),(2,8,2);
SELECT * FROM #ddd;                         

-- target x-values in table #vals (results are to go into column yy):
CREATE TABLE #vals (xx float PRIMARY KEY,yy float null, itype int);
INSERT INTO  #vals (xx) VALUES (1),(3),(4.3),(9),(12);

-- do the actual interpolation
WITH valstyp AS (
  SELECT id ii,xx,
         CASE WHEN min(x)<xx THEN CASE WHEN max(x)>xx THEN 1 ELSE 2 END ELSE 0 END flag,
         min(x) xmi,max(x) xma 
  FROM #vals INNER JOIN #ddd ON id=1 GROUP BY xx,id
), ipol AS (
  SELECT v.*,(b.x-xx)/(b.x-a.x) f,a.y ya,b.y yb 
  FROM valstyp v 
  INNER JOIN #ddd a ON a.id=ii AND a.x=(SELECT max(x) FROM #ddd WHERE id=ii 
             AND (flag=0 AND x=xmi OR flag=1 AND x<xx OR flag=2 AND x<xma))
  INNER JOIN #ddd b ON b.id=ii AND b.x=(SELECT min(x) FROM #ddd WHERE id=ii 
             AND (flag=0 AND x>xmi OR flag=1 AND x>xx OR flag=2 AND x=xma))
)
UPDATE v SET yy=ROUND(f*ya+(1-f)*yb,8),itype=flag FROM #vals v INNER JOIN ipol i ON i.xx=v.xx;

-- list the interpolated results table:
SELECT * FROM #vals

fork query

edited dec 14 17 anonymous

created mar 8 16 cars10m