How To Add Next And Subtract Preceding Row In Sql Server Based On Condition

I am trying to calculate the cumulative sum based on TranslationType column in SQL Server. Sample data: Id TransactionType Value ------------------------- 1 Receipt 10

Solution 1:

You should be summing a CASE expression which can distinguish between debits and credits:

SELECT 
    Id,
    TransactionType,
    SUM(CASE WHEN TransactionType = 'Receipt' THEN value ELSE -1.0*value END)
        OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DiffValue
FROM #Temp
ORDER BY Id;