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;