I have a table with these fields:
I need to create a table of type view where there is an additional column called Balance. The function of this column is to add or subtract the value of the column amount based on whether the value of NUM is 1 or 2 (If it is 1 sum, if it is 2 subtraction). (These data are examples, can be any other data depending on what the user captures) I have the following code with which I managed to do the previous function but not in the best way.
SELECT
R.INDICE,
R.ID,
R.NUM,
R.MONTO,
SUM(
D.MONTO *
CASE
WHEN D.NUM=2 THEN -1
ELSE 1
END
) [SALDO]
FROM TABLA R
LEFT OUTER JOIN TABLA D
ON R.ID = D.ID AND R.INDICE >= D.INDICE
GROUP BY R.INDICE,R.ID,R.NUM,R.MONTO
The result I get is the following: What I can identify is that the balance field is not calculated well when there are two consecutive expenses, but then the balance is corrected.
The column in red is the correct way how that column should be calculated. Any suggestions on what you might be doing wrong?
Update:
I applied Lamak's suggestion to my code, however, the balance column continues to be miscalculated:
SELECT * FROM VistaPrueba A
OUTER APPLY (SELECT SUM (CASE WHEN Num = 2 THEN -1 ELSE 1 END * MontoUsar) BALANCE FROM Test View WHERE IDCTA = A.IDCTA AND INDEX < = A.INDICE) B;
Result
But if I create a new table and capture the same data as the view, the balance column is displayed correctly.
SELECT *
FROM dbo.Test A OUTER APPLY (SELECT SUM (CASE WHEN NUM = 2 THEN -1 ELSE 1 END * AMOUNT) BALANCE FROM dbo.test WHERE ID = A.ID AND INDEX < = A.INDICE) B;