I have a table with below mentioned structure
create table #test
(
ID int identity(1,1),
A int,
B int
);
and having data as
ID A B
1 9 NULL
2 9 1
3 9 2
4 8 NULL
5 8 4
6 8 5
I want to calculate a new column let us say C
based on values of this table.
If we consider it as an excel table then, formula for calculation would be =IF(B1="",C1+A2,B1+A2)
.
i.e. first value of calculated column would always be A1
and then from second row I have to apply above excel formula in SQL SERVER 2012 to calculate a new column.
I am aware about LAG
and LEAD
functions, but my computation involves value of computed column of previous row for computation of current computing value.
I am clueless about such calculation in SQL SERVER. What would be the best way to compute such a condition?
EDIT Expected output would be
9
18
10
10
18
12
The select statement which I tried is
SELECT
CASE WHEN LAG(B) OVER (order by ID) is null THEN 351
ELSE LAG(B)
OVER (order by ID) + A END
FROM #test;
EDIT 2 Test Data:
INSERT INTO #test (A, B)
VALUES
(NULL,355)
,(NULL,NULL)
,(NULL,NULL)
,(NULL,NULL)
,(NULL,NULL)
,(NULL,NULL)
Result:
0
355
355
355
355
355