SQL, How to subtract from previous left over amounts, CTE?

Question

I need help in getting to the 'Taken' and 'left' columns of this image. I have tried a CTE and lag funtion but nothing seems to work.

If I have the first four columns in a table, how can I go about computing 'Taken' and 'left' column.

Basically, the way 'Taken' and 'left' is basically for each group, we have a max limit that column 4 can be taken out from. They have to be taken out in the order of A, B, C, D

Let me know if there are questions and I will try my best to answer.

Thanks

Since people are asking for given record set.. This is how it looks like. I need to find out logic for columns 'taken' and 'left'

Accountno   GroupName   MaxGroup    Amount
101          A          70           0
101          B          70           50
101          C          70          0
101          D          70          20
102          A          95          30
102          B          95           0
102          C          95           5
102          D          95           10
103          A          80           40
103          B          80          15
103          C          80          10
103          D          80           5

Answer 1

You can achieve that using OUTER APPLY as below

DECLARE @table AS TABLE(acc_no INT, [group] CHAR(1), [maxgroup] INT, amount INT)

INSERT INTO @table VALUES
(101, 'A', 70, 0),
(101, 'B', 70, 50),
(101, 'C', 70, 0),
(101, 'D', 70, 20),
(102, 'A', 95, 30),
(102, 'B', 95, 0),
(102, 'C', 95, 5),
(102, 'D', 95, 10),
(103, 'A', 80, 40),
(103, 'B', 80,15),
(103, 'C', 80, 10),
(103, 'D', 80, 5)


SELECT t.acc_no, 
    t.[group],
    t.maxgroup,
    CASE WHEN t1.assigned = 0 THEN NULL ELSE t.amount END AS taken, 
    CASE WHEN t1.assigned = 0 THEN NULL ELSE (t.maxgroup - t1.assigned) END [left]
FROM @table t
OUTER APPLY(SELECT SUM([amount]) AS assigned 
            FROM @table t1 WHERE t1.acc_no = t.acc_no AND t1.[group] <= t.[group]) t1

OUTPUT:

    acc_no  group   maxgroup    taken   left
    101     A       70          NULL    NULL
    101     B       70          50      20
    101     C       70          0       20
    101     D       70          20      0
    102     A       95          30      65
    102     B       95          0       65
    102     C       95          5       60
    102     D       95          10      50
    103     A       80          40      40
    103     B       80          15      25
    103     C       80          10      15
    103     D       80          5       10