How do I populate the Null Values based on Previous months value, when there are multiple continues-CodePudding

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

CodePudding user response：

You can build a query using the LEAD function to convert your data into ranges as the following:

from_month	to_month	Value
1	3	200
4	5	300

Then use the update join statement to update your table as the following:

Update table_name Set Value = D.Value
From table_name T Join
(
  Select Month As from_month, 
       Lead(Month, 1, (select max(month) from table_name)  1)
       Over(Order By Month) -1 As to_month,
       Value
  From table_name 
  Where Value Is Not Null
) D
On T.month Between D.from_month And D.to_month
Where T.Value Is Null;

See demo

CodePudding user response：

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.

CodePudding user response：

You can do this with a simple correlation and an updatable table expression:

update u set value = v
from (
    select *, 
        (select top(1) value 
        from t t2 where t2.month < t.month 
            and t2.value is not null 
            order by month desc
        )v
    from t
    where t.value is null
)u;