Help with field Balance in a table type view

0

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;

    
asked by Jonsin 24.04.2018 в 17:39
source

1 answer

0

Instead of a LEFT JOIN I think you should use a OUTER APPLY :

SELECT *
FROM dbo.TuTabla A
OUTER APPLY (SELECT SUM(CASE WHEN NUM = 2 THEN -1 ELSE 1 END * MONTO) SALDO
             FROM dbo.TuTabla
             WHERE ID = A.ID
             AND INDICE <= A.INDICE) B
;

Here is a demo of this query. And the results are:

╔════════╦════╦═════╦═══════╦═══════╗
║ INDICE ║ ID ║ NUM ║ MONTO ║ SALDO ║
╠════════╬════╬═════╬═══════╬═══════╣
║      4 ║  4 ║   1 ║   100 ║   100 ║
║      5 ║  4 ║   2 ║     0 ║   100 ║
║      6 ║  4 ║   2 ║   100 ║     0 ║
║      7 ║  4 ║   1 ║   100 ║   100 ║
║      8 ║  4 ║   1 ║   200 ║   300 ║
║      9 ║  4 ║   2 ║  1000 ║  -700 ║
╚════════╩════╩═════╩═══════╩═══════╝
    
answered by 24.04.2018 / 17:51
source