0

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
asked Oct 6, 2018 at 13:03
0

1 Answer 1

2

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;
answered Oct 6, 2018 at 15:19
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.