Use previous state of data I am trying to carry forward the previous state of data wherever data is not available. For example, I have following data;




Month
Order
Amount




1
Item1
100


1
Item2
200


1
Item3
300


2
NULL
NULL


3
Item4
100


4
NULL
NULL


5
NULL
NULL


6
Item1
100




So in the above example I want to use 1st month's information to be carry forwarded as it is for 2nd month. Also since 3rd month's information is available then use it for the month 4 and 5. Hence the output will be following:




Month
Order
Amount




1
Item1
100


1
Item2
200


1
Item3
300


2
Item1
100


2
Item2
200


2
Item3
300


3
Item4
100


4
Item4
100


5
Item4
100


6
Item1
100
WITH 
cte AS ( SELECT DISTINCT 
                Month, 
                MAX(CASE WHEN [Order] IS NOT NULL 
                         THEN Month 
                         END) OVER (ORDER BY Month 
                                    ROWS BETWEEN UNBOUNDED PRECEDING 
                                             AND 1 PRECEDING) prev
         FROM test )
SELECT Month, [Order], Amount
FROM test
WHERE [Order] IS NOT NULL
UNION ALL
SELECT t1.Month, t2.[Order], t2.Amount
FROM test t1
JOIN cte ON t1.Month = cte.Month
JOIN test t2 ON t2.Month = cte.prev
WHERE t1.[Order] IS NULL
ORDER BY Month, [Order];

fiddle with some explanations