Replace NULL Values in a column with Zeros on a condition

Question

I am new to SQL Server databases and queries.

I have a SQL Server database table with DateTime and Current. Current may have NULL values.

I want to replace NULL values in Current column with zeros only when either previous or next record has some value. Provided DateTime sorted in Ascending order.

Please help me to write a SQL query or a combination of stored procedure and SQL query.

Also help me to sort the existing table with DateTime in Ascending order. DateTime is not a running series.

Answer 1

You can use updatable CTEs and window functions:

with toupdate as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
update toupdate
    set current = 0
    where current is null and (prev_current is not null or next_current is not null);

If you just want an additional column in a select query (as opposed to changing the data), then:

with t as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
select t.*,
       (case when current is null and (prev_current is not null or next_current is not null)
             then 0 else current
        end) as new_current
from t;