CODEWITHSUNDEEP
sqlsql-servert-sql
Gabriel
Gabriel

Reputation: 1942

Update NULL values based on their neighbours

I have a table MyModel that looks like this:

ID | Creation
---|-----------
 1 | 2017-01-01
 2 | NULL
 3 | NULL
 4 | 2017-01-09
 5 | NULL

And so forth.

I need to give a value to Creation where it is NULL, but...

The Creation of each row needs to be greater than its predecessor.

So the following query does not work for me

update MyModel
set Creation = getdate()
where Creation is null

Because it would break the rule.

The result ought to be something like

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

What if there are not enough days to have unique dates between two values?

Giving that the Creation is datetime, there will always be a valid datetime to be inserted. The difference between records is always greater than ~30 seconds.

What if the existing values are not in order?

They are already in order. The order is based in their ID.

Upvotes: 1

Views: 75

Answers (2)

SqlZim
SqlZim

Reputation: 38023

using a common table expression with 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 demo: http://rextester.com/WAA44339

returns:

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

Upvotes: 1

S3S
S3S

Reputation: 25112

Here's a way if the gaps do not exceed the difference in days from the start and end of the gap.

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

RETURNS

+----+-------------------------+
| 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 |
+----+-------------------------+

Upvotes: 1

Related Questions