How to add and subtract value from previous rows based on condition

I have a table with values

Slno Type     Amount        
 1    P         40           
 2    C         20           
 3    P         45           
 4    P         20           
 5    C         10   

I want to get values for RESULT column.

   Type      Amount       RESULT 
    P         40           40
    C         20           20
    P         45           65
    P         20           85
    C         10           75

If Type is C then value gets subtracted from previous value, else if Type is P then value gets added to previous values.

This is what i’ve tried:

;WITH FINALMIDRESULT 
     AS (SELECT Type, 
                Value1, 
                Row_number() 
                  OVER( 
                    ORDER BY Slno ASC) rownum 
         FROM   #midRes) 
SELECT C1.Type, 
       C1.Value1, 
       CASE 
         WHEN C1.Type = 'C' THEN (SELECT Sum(Amount) 
                                  FROM   FINALMIDRESULT c2 
                                  WHERE  c2.rownum <= C1.rownum) 
         ELSE (SELECT Sum(Amount) - Sum(Amount) 
               FROM   FINALMIDRESULT c2 
               WHERE  c2.rownum <= C1.rownum) 
       END AS RESULT 
FROM   FINALMIDRESULT C1 

This is the Result that i have got

  Type      Amount       RESULT 
    P         40           0
    C         20           60
    P         45           0
    P         20           0
    C         10           135

Answer

You need to implement a seft INNER JOIN to sum all values with Slno less than the current value, like below:

;WITH   OriginalData    AS
(       SELECT  *
        FROM
        (       VALUES
                (1, 'P', 40),
                (2, 'C', 20),
                (3, 'P', 45),
                (4, 'P', 20),
                (5, 'C', 10)
        )       AS Temp(Slno, Type, Amount)
)
SELECT      [Current].Type, [Current].Amount,
            ISNULL(SUM(
            CASE    WHEN  [Previous].Type = 'P'
                    THEN +[Previous].Amount
                    ELSE -[Previous].Amount
            END),0) + 
            CASE    WHEN  [Current].Type = 'P'
                    THEN +[Current].Amount
                    ELSE -[Current].Amount
            END Result
 FROM       OriginalData [Current]
 LEFT JOIN  OriginalData [Previous]
        ON  [Previous].Slno < [Current].Slno
GROUP BY    [Current].Slno, [Current].Type, [Current].Amount
ORDER BY    [Current].Slno

I think the biggest change you can make is to shift your mindset. When you think “previous values” you chose a procedural path which can be solved my any major programming language, but rapidly evolve to a cursor approach in SQL — what isn’t appropriate in this case.

When comes to SQL, you need to think in “sets”, so you can drive your efforts to identify those data sets and combine them.