Hi I've a column in a table like this
Amount
1
2
3
4
5
6
7
I want the result of my function or stored procedure to be something like this
output
--------
1
3
6
10
15
21
28
against the above column Please help me with a solution in this regard
You can use OUTER APPLY:
CREATE TABLE #T (Amount INT);
INSERT #T (Amount) VALUES (1), (2), (3), (4), (5), (6), (7);
SELECT T.Amount, T2.Amount
FROM #T T
OUTER APPLY
( SELECT Amount = SUM(Amount)
FROM #T T2
WHERE T2.Amount <= T.Amount
) T2;
DROP TABLE #T;
Or a correlated subquery:
CREATE TABLE #T (Amount INT);
INSERT #T (Amount) VALUES (1), (2), (3), (4), (5), (6), (7);
SELECT T.Amount,
( SELECT Amount = SUM(Amount)
FROM #T T2
WHERE T2.Amount <= T.Amount
)
FROM #T T
DROP TABLE #T;
Both should yield the same plan (In this case they are essentially the same and the IO is identical).
Right, subtraction. Got there in the end, I will go through how I eventually got to the solution because it took me a while, it is not as straight forward as a cumulative sum..
First I just wrote out a query that was exactly what the logic was, essentially:
f(x) = x - f(x - 1);
So by copy and pasting the formula from the previous line I got to:
SELECT [1] = 1,
[2] = 2 - 1,
[3] = 3 - (2 - 1),
[4] = 4 - (3 - (2 - 1)),
[5] = 5 - (4 - (3 - (2 - 1))),
[6] = 6 - (5 - (4 - (3 - (2 - 1)))),
[7] = 7 - (6 - (5 - (4 - (3 - (2 - 1)))));
I then expanded out all the parentheses to give:
SELECT [1] = 1,
[2] = 2 - 1,
[3] = 3 - 2 + 1,
[4] = 4 - 3 + 2 - 1,
[5] = 5 - 4 + 3 - 2 + 1,
[6] = 6 - 5 + 4 - 3 + 2 - 1,
[7] = 7 - 6 + 5 - 4 + 3 - 2 + 1;
As you can see the operator alternates between + and - for each amount as you move down (i.e. for 5 you add the 3, for 6 you minus the 3, then for 7 you add it again).
This means you need to find out the position of each value to work out whether or not to add or subtract it. So using this:
SELECT T.Amount,
T2.RowNum,
T2.Amount
FROM #T T
OUTER APPLY
( SELECT Amount, RowNum = ROW_NUMBER() OVER(ORDER BY Amount DESC)
FROM #T T2
WHERE T2.Amount < T.Amount
) T2
WHERE T.Amount IN (4, 5)
You end up with:
Amount RowNum Amount
-------------------------
4 1 3
4 2 2
4 3 1
-------------------------
5 1 4
5 2 3
5 3 2
5 4 1
So remembering the previous formala for these two:
[4] = 4 - 3 + 2 - 1,
[5] = 5 - 4 + 3 - 2 + 1,
We can see that where RowNum is odd we need to - the second amount, where it is even we need to add it. We can't use ROW_NUMBER() inside a SUM function, so we then need to perform a second aggregate, giving a final query of:
SELECT T.Amount,
Subtraction = T.Amount - SUM(ISNULL(T2.Amount, 0))
FROM #T T
OUTER APPLY
( SELECT Amount = CASE WHEN ROW_NUMBER() OVER(ORDER BY Amount DESC) % 2 = 0 THEN -Amount ELSE Amount END
FROM #T T2
WHERE T2.Amount < T.Amount
) T2
GROUP BY T.Amount;
