Add or subtract one column based on the value of another and making cut by ID

Question

Add or subtract one column based on the value of another and making cut by ID

Navigation

#1 by (0 votes)

0

I have a table called Registers with this structure:

What I do is add a new column to the right where a balance is going to be calculated (either add or subtract) based on the value of each record in the Concept column; something like this:

As you can see, I need the calculation to start from the amount of the first record of each ID, that is, as if it were making a cut and based on the subsequent records the calculation is made. For example when the concept is 1 you must add and for 2 and 3 you must subtract.

I have this query but it marks me an error in order and if I remove it in the additional column, balance puts the same amount in all the records, that is, neither adds nor subtracts: (

SELECT
    ID, Concepto, Fecha, Importe,
    SUM(Importe * case when Concepto = 1 then 1 else  -1 end) 
   OVER(PARTITION BY ID ORDER BY Fecha ) AS Saldo
FROM Registros
ORDER BY ID, Fecha;

tsql sql-server-2008-r2

asked by Jonsin 24.01.2018 в 00:31

source

1 answer

what should I do so that the FCM push notifications do not reach me when in my app I close session? CrystalReportViwer does not show the report

score 0 · Accepted Answer

It would only be necessary to perform:

SELECT 
    L.ID,
    L.CONCEPTO,
    L.FECHA,
    L.IMPORTE,    
    SUM(
        R.IMPORTE *
        CASE 
            WHEN R.CONCEPTO IN(2, 3) THEN -1 
            ELSE 1
        END
       ) [SALDO]
FROM REGISTRO L
LEFT JOIN REGISTRO R
    ON L.ID = R.ID AND L.FECHA >= R.FECHA
GROUP BY L.ID, L.CONCEPTO, L.FECHA, L.IMPORTE
ORDER BY L.ID, L.FECHA

DEMO

To the commented, there would be a problem in the case that there is no column that is considered as consecutive. For this, you could do:

;WITH REG_ENUMERADO AS(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY FECHA ASC) [INDICE],
        ID,
        CONCEPTO,
        FECHA,
        IMPORTE
    FROM REGISTROS
)
SELECT 
    L.ID,
    L.CONCEPTO,
    L.FECHA,
    L.IMPORTE,    
    SUM(
        R.IMPORTE *
        CASE 
            WHEN R.CONCEPTO IN(2, 3) THEN -1 
            ELSE 1
        END
       ) [SALDO]
FROM REG_ENUMERADO L
LEFT JOIN REG_ENUMERADO R
    ON L.ID = R.ID AND L.INDICE >= R.INDICE
GROUP BY L.ID, L.CONCEPTO, L.FECHA, L.IMPORTE
ORDER BY L.ID, L.FECHA