I am trying to calculate the cumulative sum based on TranslationType column in SQL Server.
Sample data:
Id TransactionType Value ------------------------- 1 Receipt 10 2 Issue 2 3 Receipt 10 4 Issue 5 5 Issue 3
I tried but I have a problem the output I am getting is wrong :
Id TransactionType DiffValue ----------------------------- 1 Receipt 10 2 Issue 8 3 Receipt 22 4 Issue 5 5 Issue 2
Desired output of the difference value:
Id TransactionType Value DiffValue --------------------------------------- 1 Receipt 10 10 2 Issue 2 8 if issue then 10-2 3 Receipt 10 18 if receipt then 10+8 4 Issue 5 13 if issue then 18-5 5 Issue 3 10 if issue then 13-3
SQL create scripts:
DROP TABLE #Temp
CREATE TABLE #Temp
(
Id INT,
TransactionType VARCHAR(50),
value INT,
)
INSERT INTO #Temp (Id, TransactionType, value)
VALUES (1, 'Receipt', 10), (2, 'Issue', 2), (3, 'Receipt', 10),
(4, 'Issue', 5), (5, 'Issue', 3)
SELECT * FROM #Temp
My query attempt:
SELECT
Id,
TransactionType,
CASE
WHEN TransactionType = 'Receipt'
THEN SUM(value) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE LAG(value) OVER (ORDER BY Id) - value
END AS DiffValue
FROM
#Temp
Advertisement
Answer
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;