SQL Server – Calculate Conditional Running Totals

running-totalssql serversql-server-2012

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

Best Answer

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;
Related Question