基于其邻居更新空值

这里有一种方法,如果差距不超过从差距开始和结束的天数差。

declare @table table (ID int, Creation datetime)
insert into @table
values
(1,'2017-01-01'),
(2,NULL),
(3,NULL),
(4,'2017-01-09'),
(5,null),
(6,'2017-01-11'),
(7,null)

update t
set t.Creation = isnull(dateadd(day,(t2.id - t.ID) * -1,t2.creation),getdate())
from @table t
left join @table t2 on t2.ID > t.ID and t2.Creation is not null
where t.Creation is  null

select * from @table

退货

+----+-------------------------+
| ID |        Creation         |
+----+-------------------------+
|  1 | 2017-01-01 00:00:00.000 |
|  2 | 2017-01-07 00:00:00.000 |
|  3 | 2017-01-08 00:00:00.000 |
|  4 | 2017-01-09 00:00:00.000 |
|  5 | 2017-01-10 00:00:00.000 |
|  6 | 2017-01-11 00:00:00.000 |
|  7 | 2017-10-25 11:58:56.353 |
+----+-------------------------+

使用 common table expression row_number()

;with cte as (
  select *
    , rn = row_number() over (order by id)
  from mymodel
)
update cte
  set Creation = dateadd(day,cte.rn-x.rn,x.Creation)
from cte 
  cross apply (
    select top 1 *
    from cte i
    where i.creation is not null
      and i.id < cte.id
    order by i.id desc
    ) x
where cte.creation is null;

select * 
from mymodel;

rextester演示: http://rextester.com/WAA44339

返回:

+----+------------+
| id |  Creation  |
+----+------------+
|  1 | 2017-01-01 |
|  2 | 2017-01-02 |
|  3 | 2017-01-03 |
|  4 | 2017-01-09 |
|  5 | 2017-01-10 |
+----+------------+