TSQL update NULLs only when between the same values

Question

I have a table where I need to fill in the NULLs with data. However, I only want to replace the NULL if the before and after values are the same.

G  ID   Val    NewValue
a   1   N       N
a   2   N       N
a   3   NULL    NULL   -- notice this is the last value of group 'a' so keep NULL and if NULL was the first of the group, keep NULL as well

b   4   P       P
b   5   N       N
b   6   NULL    N     -- N before and N after so the new value ='N'
b   7   N       N

c   8   N       N
c   9   N       N
c   10  NULL    NULL  -- N before but P after so keep NULL
c   11  P       P
c   12  N       N
c   13  N       N
c   14  N       N

d   15  P       P
d   16  NULL    P    --  P before and P after (the series) so make 'P'
d   17  NULL    P    --  P before and P after (the series) so make 'P'
d   18  P       P
d   19  N       N

So what I have a solution for the easy part. The query below gives:

• Correct results for IDs = 6, 16, and 17
• Incorrect results for IDs = 3 and 10

So my question is how to do the rest?

DECLARE @Table TABLE(
    G varchar(1),
    ID INT,
    Val varchar(2)
    )
INSERT INTO @Table VALUES 
('a',1,'N'),('a',2,'N'),('a',3, NULL),
('b',4,'P'),('b',5,'N'),('b',6, NULL),('b',7,'N'),
('c',8, 'N'),('c',9, 'N'),('c',10,NULL),('c',11,'P'),('c',12,'N'),('c',13,'N'),('c',14,'N'),
('d',15, 'P'),('d',16, NULL),('d',17,NULL),('d',18,'P'),('d',19,'N')
SELECT *,
CASE WHEN Val IS NULL 
     THEN (SELECT TOP 1 Val FROM @Table WHERE ID<T.ID AND G=T.G AND Val IS NOT NULL ORDER BY ID DESC) 
     ELSE Val END AS NewVal
FROM @Table T

ORDER BY G, ID

Answer 1

The most important thing when you solve problems is to understand the approach in plain English. Here, for example, all you need is to find two closest non-NULL values, the most preceding and the least subsequent. When they exist and are equal, use their value.

Here is a translation of this approach into SQL:

select t.*, isnull(t.Val, case when pr.Val = nr.Val then pr.Val end) as [NewVal]
from @table t
    outer apply (
        select top (1) tp.Val from @table tp
        where tp.G = t.G and tp.Val is not null and tp.Id < t.Id
        order by tp.Id desc
    ) pr
    outer apply (
        select top (1) tn.Val from @table tn
        where tn.G = t.G and tn.Val is not null and tn.Id > t.Id
        order by tn.Id
    ) nr
order by t.g, t.Id;