MS SQL 2005 - Replace Zero Values ​​with Linear Interpolation

Question

I have a dataset below with a date and value in MS SQL 2005. The values ​​for some dates are NULL. What is the best way to populate Null values ​​with linear interpolation?

Date,ShortName,LongName,Value 12/31/2012,ABC,Test1,-4.0 12/31/2012,XYZ,Test2,-8.1 1/2/2013,ABC,Test1,NULL 1/2/2013,XYZ,Test2,NULL 1/3/2013,ABC,Test1,NULL 1/3/2013,XYZ,Test2,NULL 1/4/2013,ABC,Test1,-9.6 1/4/2013,XYZ,Test2,-13.0 1/7/2013,ABC,Test1,NULL 1/7/2013,XYZ,Test2,NULL 1/8/2013,ABC,Test1,NULL 1/8/2013,XYZ,Test2,NULL 1/9/2013,ABC,Test1,NULL 1/9/2013,XYZ,Test2,NULL 1/10/2013,ABC,Test1,NULL 1/10/2013,XYZ,Test2,NULL 1/11/2013,ABC,Test1,-7.1 1/11/2013,XYZ,Test2,-12.7 

Answer 1

Here's one approach that seems to give me decent results:

 select * from tests where value is not null union all select t.Date , t.ShortName , t.LongName , Value = p.Value + (n.Value - p.Value) * (cast(datediff(dd, p.Date, t.Date) as decimal(16,1))) / (cast(datediff(dd, p.Date, n.Date) as decimal(16,1))) from tests t cross apply ( select top 1 p.date, p.value from tests p where p.Value is not null and t.shortname = p.shortname and t.date > p.date order by p.date desc ) p cross apply ( select top 1 n.date, n.value from tests n where n.Value is not null and t.shortname = n.shortname and t.date < n.date order by n.date ) n where t.Value is null order by ShortName, Date 

SQL Fiddle with a demo .

Another SQL Fiddle with more debugging info, i.e. the x, x0, y, etc. values ​​that I used.

Although your data does not have a weekend, I suggested that the line will include weekends. If not, I'm sure the request can be modified.

Answer 2

It is unclear whether you want to interpolate only each short name or the entire date range. Here is a simpler date range, but answer back if you also want to split on ShortName.

 declare @Table table ([Date] datetime, ShortName varchar(100), LongName varchar(100), Value decimal(10,2)); insert into @Table select '12/31/2012','ABC','Test1','-4.0' union all select '12/31/2012','XYZ','Test2','-8.1' union all select '1/2/2013','ABC','Test1',NULL union all select '1/2/2013','XYZ','Test2',NULL union all select '1/3/2013','ABC','Test1',NULL union all select '1/3/2013','XYZ','Test2',NULL union all select '1/4/2013','ABC','Test1','-9.6' union all select '1/4/2013','XYZ','Test2','-13.0' union all select '1/7/2013','ABC','Test1',NULL union all select '1/7/2013','XYZ','Test2',NULL union all select '1/8/2013','ABC','Test1',NULL union all select '1/8/2013','XYZ','Test2',NULL union all select '1/9/2013','ABC','Test1',NULL union all select '1/9/2013','XYZ','Test2',NULL union all select '1/10/2013','ABC','Test1',NULL union all select '1/10/2013','XYZ','Test2',NULL union all select '1/11/2013','ABC','Test1','-7.1' union all select '1/11/2013','XYZ','Test2','-12.7' ;with stage as ( select *, [r] = row_number() over (order by [Date]) from @Table ) select l.[Date], [OldValue] = l.value, [NewValue] = isnull(l.Value, f.Value + (t.Value - f.Value) * (l.[r] - f.[r]) / (t.[r] - f.[r])) from stage l outer apply ( select top 1 [Date], Value, [r] from stage x where x.[Date] < l.[Date] and x.Value is not null order by [Date] desc ) f outer apply ( select top 1 [Date], Value, [r] from stage x where x.[Date] > l.[Date] and x.Value is not null order by [Date] asc ) t order by [date] asc;