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
1 Answer 1
Below is a CTE method to facilitate evaluating values from the previous row with LAG
.
CREATE TABLE #test
(
ID int IDENTITY
, A int
, B int
);
INSERT INTO #test (A, B)
VALUES
(9, NULL)
, (9, 1)
, (9, 2)
, (8, NULL)
, (8, 4)
, (8, 5);
WITH test AS (
SELECT
ID
, A
, LAG(A) OVER(ORDER BY ID) AS prev_A
, LAG(B) OVER(ORDER BY ID) AS prev_B
, A + COALESCE(LAG(B) OVER(ORDER BY ID), 0) AS C
FROM #test
)
SELECT
A + COALESCE(CASE
WHEN prev_B IS NOT NULL THEN prev_B
ELSE LAG(C) OVER(ORDER BY ID)
END, 0) AS C
FROM test
ORDER BY ID;
EDIT
To handle the NULL A values in the edited question, try the recursive CTE method below.
WITH
curr AS (
SELECT
A
, B
, ROW_NUMBER() OVER (ORDER BY ID) AS rownum
FROM #test
)
, prev AS (
SELECT
A
, B
, COALESCE(A, 0) AS C
, rownum
FROM curr
WHERE rownum = 1
UNION ALL
SELECT
curr.A
, curr.B
, CASE
WHEN prev.B IS NULL THEN prev.C + COALESCE(curr.A, 0)
ELSE prev.B + COALESCE(curr.A, 0)
END AS C
, curr.rownum
FROM prev
JOIN curr ON curr.rownum = prev.rownum + 1
)
SELECT C
FROM prev;
Explore related questions
See similar questions with these tags.