Condition sums of a select

Question

Condition sums of a select

Navigation

#1 by (1 votes)

1

I have a table with the following structure:

codigo / periodo / signo / importe

I want to get the following:

codigo / saldo_anterior / cargo_actual / abono_actual / saldo

The period field is integer (YYYYMM) If for example I am in the month of November, period would be 201711. The Previous_Balance considers the records that have the period until 201710. Current_Load and Current_Add are for the period = 201711. The Charge and Credit are the amounts obtained from the sign: "+" = charge; "-" = subscription. How do I condition the period in the select that does this in a generic way?

codigo / cargo / abono / saldo

This is the select:

SELECT  codigo,
        SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) cargo,
        SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) abono,
        SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) - 
        SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) saldo
FROM tabla
GROUP BY codigo
HAVING SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) - 
       SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) <> 0;

The structure of the table:

ID  codigo  periodo signo   importe
4521    100 20179   +   200.00
4522    200 20179   -   80.00
4523    300 20179   -   70.00
4525    100 201710  +   250.00
4526    200 201710  +   120.00
4527    300 201710  +   350.00
4528    100 201710  -   100.00
4529    300 201710  -   15.00
4530    200 201711  +   170.00
4531    300 201711  +   80.00
4532    500 201711  +   300.00
4533    100 201711  -   110.00
4534    200 201711  -   150.00
4535    300 201711  -   170.00

The calculation of previous balance (in this case) should be until the period 201710 and then, current charge, current payment are for the period 201711. The balance is the difference.

mysql sql

asked by Piropeator 09.11.2017 в 17:45

source

1 answer

Frame widths with Flexbox in IE11 How to check if a hidden input has been tampered with?

score 1 · Accepted Answer

This should not be very complicated, but it becomes complex exclusively because of how you are saving the periodo . Being int in YYYYMM format, makes to calculate the previous period, you have to concatenate a 01 , then convert it to date, then subtract a month, and then convert it back to number.

It would be something like this:

SELECT  codigo,
        SUM(CASE 
                WHEN signo = '+' 
                AND t1.periodo = DATE_FORMAT(STR_TO_DATE(CONCAT(t2.MaxPeriodo,'01'),'%Y%m%d') - INTERVAL 1 MONTH,'%Y%m') 
                THEN importe 
            ELSE 0 END) - 
        SUM(CASE 
                WHEN signo = '-' 
                AND t1.periodo = DATE_FORMAT(STR_TO_DATE(CONCAT(t2.MaxPeriodo,'01'),'%Y%m%d') - INTERVAL 1 MONTH,'%Y%m') 
                THEN importe 
                ELSE 0 
            END) saldo_anterior,
        SUM(CASE 
                WHEN signo = '+' 
                AND t1.periodo = t2.MaxPeriodo
                THEN importe 
                ELSE 0 
            END) cargo_actual,
        SUM(CASE 
                WHEN signo = '-' 
                AND t1.periodo = t2.MaxPeriodo
                THEN importe 
                ELSE 0 
            END) abono_actual,
        SUM(CASE 
                WHEN signo = '+' 
                AND t1.periodo = t2.MaxPeriodo
                THEN importe 
                ELSE 0 
            END) -
        SUM(CASE 
                WHEN signo = '-' 
                AND t1.periodo = t2.MaxPeriodo
                THEN importe 
                ELSE 0 
            END) saldo_actual
FROM tabla t1
CROSS JOIN (SELECT MAX(periodo) MaxPeriodo
            FROM tabla) t2
WHERE t1.periodo IN (t2.MaxPeriodo,DATE_FORMAT(STR_TO_DATE(CONCAT(t2.MaxPeriodo,'01'),'%Y%m%d') - INTERVAL 1 MONTH,'%Y%m'))
GROUP BY codigo
;