sql – Replacing NULL with the value of the previous value

Question:

I make the following query in t-sql:

SELECT *, 
CASE WHEN Sum_ IS NOT NULL THEN Sum_
ELSE COALESCE(Sum_, 0)
END AS first_Non_Null
FROM dbo.VIEW_1;
GO

Where is the processing of the Sum_ field, if it is empty, then we replace it with 0, otherwise we leave it unchanged. My question is, can I make it so that the insertion is not 0, but the value of the previous numerical value.

If, for example, this query returns:

Index_data | Sum_
_____________________
20.10.2015 | 8
21.10.2015 | 2
22.10.2015 | NULL
23.10.2015 | 5
24.10.2015 | NULL
25.10.2015 | NULL
26.10.2015 | 6

Change it like this:

Index_data | Sum_
_____________________
20.10.2015 | 8
21.10.2015 | 2
22.10.2015 | 2
23.10.2015 | 5
24.10.2015 | 5
25.10.2015 | 5
26.10.2015 | 6

How to do it? I wanted to sort it out with the cursor, however, I want to issue it simply with a query or nested queries.

SELECT *, 
CASE WHEN Sum_ IS NOT NULL THEN Sum_
ELSE COALESCE(Sum_, SELECT Sum_ FROM dbo.VIEW_1 WHERE ????)
END AS first_Non_Null
FROM dbo.VIEW_1;
GO

or something like this:

DECLARE @val nvarchar(30); 
SELECT *, 
CASE WHEN Sum_ IS NOT NULL THEN Sum_
ELSE COALESCE(Sum_, Sum_.PREV....... )
END AS first_Non_Null
FROM dbo.VIEW_1;
GO

Is this possible to do?

Answer:

Use the LAG function:

SELECT index_data, 
coalesce(Sum_, LAG(Sum_)  OVER(ORDER BY index_data))
FROM dbo.VIEW_1;
Scroll to Top