How to modify a daily statement for a monthly statement?

Navigation
0

I have a table that is not in normal database forms, but has old data that I need to get.

I managed to get you to submit the daily statement, but now I need to modify it to generate the monthly statement in the following query: 

SELECT dia, mes, ano, credito, debito, format(saldo,2) as saldo
FROM (
    SELECT
        dia,
        mes,
        ano,
        credito,
        debito,
    @dc := dc AS tipo,
    @saldo := IF(@dc = 'C', @saldo + credito, @saldo - debito) AS saldo
    FROM lc_movimento, (SELECT @dc := 'C', @saldo := 0) as vars
    ORDER BY ano, mes, dia
) AS extrato

This is the demo link:

Table in SQL Fiddle

This is the desired result. 

    mês   ano     credito   debeito          saldo
    5     2014     2500     8722,6          -6222,6
    6     2014     0        12792,96        -19015,56
    7     2014     0        10884,82        -29900,38
    8     2014     0        10884,82        -40785,2
    9     2014     0        10022,68        -50807,88
    5     2015     650      7580,25         -57738,13
    
asked by anonymous 08.09.2015 / 04:46

1 answer

2

I was able to get the result but I had to create a VIEW. I used a @bigown hint and it worked.

It stinks, but it worked.

Follow the link demo SQL Fiddle . 

SELECT DATE_FORMAT(data,'%d/%m/%Y') AS data,
    SUM(IF(tipo = 'D', valor, 0)) AS debito,
    SUM(IF(tipo = 'C', valor, 0)) AS credito,
    (SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
         WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
    ) AS saldo
FROM vw_extrato
GROUP BY MONTH(data), YEAR(data) ORDER BY data desc
    
09.09.2015 / 04:31
Graphic Raphael.js error Media queries do not work