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
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;
0

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.