LERP between two dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

LERP between two dates

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-08-15 : 18:26:59

Hi

I got the data



LogTime               |  Value
2011-01-01 13:00:00   |  10
2011-01-01 14:00:00   |  NULL
2011-01-01 15:00:00   |  40
2011-01-01 16:00:00   |  NULL
2011-01-01 17:00:00   |  NULL
2011-01-01 18:00:00   |  NULL
2011-01-01 19:00:00   |  120

And i need to fill the NULL value with
interpolating data between this records.

so it looks more like this:



LogTime               |  Value
2011-01-01 13:00:00   |  10
2011-01-01 14:00:00   |  25
2011-01-01 15:00:00   |  40
2011-01-01 16:00:00   |  60
2011-01-01 17:00:00   |  80
2011-01-01 18:00:00   |  100
2011-01-01 19:00:00   |  120

How can it be possible to doing that?
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-15 : 19:54:03

[code]
with
LogTbl as
(
select *, row_no = row_number() over (order by LogTime)
from LogTable
)
select l.LogTime, Value = coalesce(l.Value, f.Value + (t.Value - f.Value) * (l.row_no - f.row_no) / (t.row_no - f.row_no))
from LogTbl l
outer apply
(
select top 1 LogTime, Value, row_no
from LogTbl x
where x.LogTime < l.LogTime
and x.Value is not null
order by LogTime desc
) f
outer apply
(
select top 1 LogTime, Value, row_no
from LogTbl x
where x.LogTime > l.LogTime
and x.Value is not null
order by LogTime
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-08-16 : 05:09:16

Thank you for answering.

It looks a little complicated, but it should work properly.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources