How to generate an extract per month in the database?

Asked

Viewed 2,263 times

2

I have to generate a statement in the database that is similar to the bank statement and present the cumulative balance.

I have the following columns

Data, Debito, Crédito.

Data entry is daily but the statement I need is per month.

01/01/2015 – Credito 100
02/01/2015 – Débido 30
02/01/2015 – credito 70
01/02/2015 – debito 50
01/02/2015 – credito 80
02/02/2015 – debito 20
01/03/2015 – credito 60
01/03/2015 – debito 20

data        debito  credito saldo
01/01/2015          100     100
02/01/2015  30              70
02/01/2015          70      140
01/02/2015  50              90
01/02/2015          80      170
01/02/2015  20              150
01/03/2015          60      210
01/03/2015  20              190

The result that should be:

data    debito  credito saldo
jan     30      170     140
fev     70      80      150
mar     20      60      190
mysql sql
  • 1

    Have you done in the format without grouping per month? Put there how is the query then it is easier to adapt only the part you are in doubt.

  • Hello I haven’t done anything yet I’m trying to elaborate yet.

  • The Balance is not accumulating, he has to catch the movement to land to generate the new balance... SELECT sum(value) the value, sum(nfvalue) the credit, (sum(nfvalue)-sum(value)) the balance, month, year FROM lc_movimento WHERE idemp = '22' and (cat ='149' or cat ='170') GROUP BY mes, year ORDER BY year, month, day

  • This is already the answer?

2 answers

3


To make the balance would do so:

SELECT DATE_FORMAT(data,'%d/%m/%Y') AS data,
    IF(tipo = 'D', valor, '') AS debito,
    IF(tipo = 'C', valor, '') AS credito,
    (SELECT SUM(IF(tipo = 'C', valor, -valor))
        FROM Lancamento L2
        WHERE L2.id <= Lancamento.id) AS saldo
FROM Lancamento

Behold working in the Sqlfidle. Also put on the Github for future reference.

This way is not very efficient but solves. In a production system I would think of something better.

To group by month would:

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 Lancamento AS L2
         WHERE DATE_FORMAT(Lancamento.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
    ) AS saldo
FROM Lancamento
GROUP BY MONTH(data), YEAR(data)

Behold working in the Sqlfiddle. Also put on the Github for future reference.

I don’t really trust this solution but it seems to solve what was asked.

I would particularly adopt a solution by taking these releases and mounting the extract in PHP. I’m not saying I should do this, but in my specific case, I do better with imperative than declarative languages. I would do it faster, thinking very little, I would trust the result more, it would be more efficient and easier to maintain. You can probably make the SQL code better but I won’t spend any more time on this.

I would probably have a different structure which would facilitate the consultation.

  • This is my real problem, the year is in different columns type: day, month, year, the debit and credit also, and there is a column dc for D = debit and C = credit, and has the columns debit and credit, the table is structured so and from it I need to assemble the statement, mysql and your solution was ideal, can you help me? follow the link http://www.sqlfiddle.com/#! 9/7c6744/1/0 @bigown

  • 2

    If you are different from what is in the question becomes complicated. Open another question. I do not guarantee that I can mess with it but if it does I see. Put all this on. Esteem people help. Chew as much as possible and increase your chances of getting answer. No one likes to have to do everything by hand. Are you on the right track. Did you structure this table or did you pick it up like this? Is this a real job or exercise? This table has a huge amount of errors.

  • Hello, and exactly this and a real table, it is not in the normal forms, I agree but as there is a programming and there is data since 2010, I am trying to get the extract with itself, your examples were very valid and is really what I need, I’ll try harder to solve this problem. thanks for the examples getting the desired result I will publish. @bigown

1

I got the result wait, my table is not in the normal forms, but it would be very expensive to have to reprogram.

follows the expected result link.

SQL Fiddle

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

  • Actually the table has several problems, the normalization is the smallest of them. Probably the most serious is to use double to store monetary values. This is inaccurate and can cause harm. It can even generate a lawsuit against the company. http://answall.com/a/38140/101

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.