I am trying to calculate the cumulative sum based on TranslationType
column in SQL Server.
Sample data:
x
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;