CODEWITHSUNDEEP
sqlsql-server
Magesh
Magesh

Reputation: 11

How to interpolate in sql server?

I'm using MS Visual Studio 2017 windows form application and SQL Server. For example, here is a Table with data, 

ColumnA | ColumnB
1099    | 57
1209    | 58
1213    | 59
1227    | 60
1228    | 61

If we pass the exact value (that is) 1213 in ColumnA, I can able to get 59 from columnB data. Now, how to interpolate if we pass 1210 in ColumnA: 

(1210-1209)/(1213-1210) = (x-58)/(59-x) ==> x = 58.25

Kindly help to code this. Thanks in advance.

Upvotes: 1

Views: 153

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37440

Try below query:

declare @tbl table (ColA int, ColB int);
insert into @tbl values
(1099 , 57),
(1209 , 58),
(1213 , 59),
(1227 , 60),
(1228 , 61);

declare @toInterpolate int = 1210;

select min(colB) + (@toInterpolate - min(ColA))*1.0/(max(colA) - min(colA))
from (
    select top 2 ROW_NUMBER() OVER (PARTITION BY CASE WHEN ColA > @Value THEN 1 ELSE 0 END ORDER BY ABS(ColA - @Value)) rn,
                 ColA,
                 ColB
    from @tbl
    order by rn
) a

NOTE: I assumed, that values in ColumnB are increasing.

Upvotes: 2

Daniel Brughera
Daniel Brughera

Reputation: 1651

Try this one

CREATE TABLE #YourTable (ColumnA float, ColumnB float)
INSERT #YourTable values
(1099 , 57),
(1209 , 58),
(1213 , 59),
(1227 , 60),
(1228 , 61);

DECLARE @Value float = 1221

select *, a.ColumnB + (@Value - a.ColumnA)/ISNULL(NULLIF((b.ColumnA - a.ColumnA),0),1)
from (
    select top 1 ColumnA,
                 ColumnB
    from #YourTable
    where ColumnA <= @Value
    order by ColumnA desc
) a
cross join (
    select top 1 ColumnA,
                 ColumnB
    from #YourTable
    where ColumnA >= @Value
) b

DROP TABLE #YourTable

Upvotes: 0

Related Questions