How do I populate the Null Values based on Previous months value, when there are multiple continues Null values in SQL

Question

Below is the input table:

Month	Value
1	200
2	-
3	-
4	300
5	-

Expected Output :

Month	Value
1	200
2	200
3	200
4	300
5	300

I did try using LAG function in SQL, as a result I was able to populate value for the immediate NULL values which is Month 2 in above case but next month which is Month 3 was still having Null values

Answer 1

You can emulate the LAST_VALUE function to ignore nulls by creating partitions with:

• one non-null value
• following null values (excluding next non-null value)

then updating null values to the max for each partition.

WITH cte AS (
    SELECT *, COUNT(Value) OVER(ORDER BY Month) AS partitions
    FROM tab
), cte2 AS (
    SELECT Month, MAX(Value) OVER(PARTITION BY partitions) AS Value 
    FROM cte
)
UPDATE tab
SET tab.Value = cte2.Value
FROM cte2
WHERE tab.Month = cte2.Month;

Check the demo here.