SALDO DE DEBITO E CREDITO NO MESMO REGISTRO - MYSQL

MySQL

Banco de Dados

14/09/2018

Prezados amigos, estou precisando de ajuda para o seguinte problema em MYSQL:

Possuo uma Tabela chamada "lancamento" onde guardo todos os meus lançamentos contábeis.
tenho as seguintes colunas (tenho mais colunas, isso é só o exemplo Básico):
campos: id , empresa, data, evento, descricao, tipo, conta_debito, conta_credito, valor, saldo
valores: 1, 10, 01/05, 2, Rcto.NF 553, 1, 11110, 41105, 300,00, 0
valores: 2, 10, 02/05, 1, pgto.NF 332, 2, 51120, 11110, 150,00, 0
valores: 3, 10, 03/05, 6, Devoluçao, 1, 11110, 51120, 200,00, 0

O que eu preciso é somar todos os valores de débito e crédito de cada conta, colocando-os em ordem e mostrando o total da coluna debito, o total da coluna credito e calcular o saldo.

o que eu quero é este resultado:
conta | total_debito | total_credito | saldo
11110 | 500,00 | 150,00 | 350,00 D
41105 | 0,00 | 300,00 | 300,00 C
51120 | 150,00 | 200,00 | 50,00 C

e assim sucessivamente para todas as contas da tabela.

alguém ajuda por favor.
Wilton Santos

Wilton Santos

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

14/09/2018

veja se isto funciona pra você:
select
	conta, total_debito, total_credito,
	abs(total_debito - total_credito) saldo,
	(case when total_debito > total_credito then 'D' else 'C' end) tp
from (
	select
		conta,
		sum(case when tipo='D' then valor else 0 end) total_debito, 
		sum(case when tipo='C' then valor else 0 end) total_credito,
	from (
			select 'D' tipo, conta_debito conta, valor
			from lancamento
			where not conta_debito is null
			union all
			select 'C' tipo, conta_credito, valor
			from lancamento
			where not conta_credito is null
	) tab
	group by
		conta
) resultado

a instrução acima foi implementada com base na sintaxe do SQL Server, mas acredito que funciona no MySQL.
GOSTEI 4

Mais Respostas

Wilton Santos

Wilton Santos

14/09/2018

MELHORANDO O EXEMPLO:
IMAGINE OS SEGUINTES REGISTROS NA TABELA LANCAMENTO
 id   | empresa | data     | evento | descricao      | tipo | conta_debito | conta_credito |    valor    |  saldo
 1    |   10    |  01/05   |  2     |  Rcto.NF 553   |  1   |    11110     |    41105      |  300,00     |    0
 2    |   10    |  02/05   |  1     |  Pgto.NF 332   |  2   |    51120     |    11110      |  150,00     |    0
 3    |   10    |  03/05   |  1     |  Devolucao     |  1   |    11110     |    51120      |  200,00     |    0


RESULTADO ESPERADO:

conta | total_debito | total_credito | saldo
11110 |    500,00    |    150,00     | 350,00 D
41105 |      0,00    |    300,00     | 300,00 C
51120 |    150,00    |    200,00     |  50,00 C
GOSTEI 2
Wilton Santos

Wilton Santos

14/09/2018

Rapaz o código ficou show, funcionou beleza do jeito que eu queria. Eu gostaria que você explicasse só para eu entender os detalhes da "subquery" do segundo ''FROM'', se possivel. Outro detalhe seria a especificação da empresa, quero essas informacoes apenas da empresa 10 (por exemplo). Caso eu precise também fazer um JOIN com a tabela CTA_CONTAS para mostrar o nome da conta (cta_nome) além do código já mostrado, onde incluiria?

Desde ja agradeço. Show de Bola! Salvou meu dia!
GOSTEI 1
Emerson Nascimento

Emerson Nascimento

14/09/2018

A instrução
            select 'D' tipo, conta_debito conta, valor
            from lancamento
            where not conta_debito is null
            union all
            select 'C' tipo, conta_credito, valor
            from lancamento
            where not conta_credito is null

irá transformar isto:
id   | empresa | data     | evento | descricao      | tipo | conta_debito | conta_credito |    valor    |  saldo
1    |   10    |  01/05   |  2     |  Rcto.NF 553   |  1   |    11110     |    41105      |  300,00     |    0
2    |   10    |  02/05   |  1     |  Pgto.NF 332   |  2   |    51120     |    11110      |  150,00     |    0
3    |   10    |  03/05   |  1     |  Devolucao     |  1   |    11110     |    51120      |  200,00     |    0

Nisto:
tipo | conta  |    valor |
D    | 11110  |   300,00 |
C    | 41105  |   300,00 |
D    | 51120  |   150,00 |
C    | 11110  |   150,00 |
D    | 11110  |   200,00 |
C    | 51120  |   200,00 |

Ou seja, irá colocar os valores das colunas em linhas, para que possam ser agrupadas.

Depois, a instrução
    select
        conta,
        sum(case when tipo='D' then valor else 0 end) total_debito, 
        sum(case when tipo='C' then valor else 0 end) total_credito,
    from (
            [....]
    ) tab
    group by
        conta

Irá transformar o resultado anterior, neste resultado, com os valores agrupados por conta contábil:
conta  |   total_debito  |  total_credito |
11110  |         500,00  |         150,00 |
51120  |         150,00  |         200,00 |
41105  |           0,00  |         300,00 |


E, por fim, a instrução
select
    conta, total_debito, total_credito,
    abs(total_debito - total_credito) saldo,
    (case when total_debito > total_credito then 'D' else 'C' end) tp
from (
        [....]
) resultado

irá apenas efetuar o calculo do saldo e seu tipo (credor ou devedor)
conta  |   total_debito  |  total_credito |     saldo | tp |
11110  |         500,00  |         150,00 |    350,00 |  D |
51120  |         150,00  |         200,00 |     50,00 |  C |
41105  |           0,00  |         300,00 |    300,00 |  C |
GOSTEI 1
Emerson Nascimento

Emerson Nascimento

14/09/2018

Quanto ao relacionamento com a tabela de contas contábeis, faça após obter todo o resultado, por exemplo:
select
    r.conta, cc.descricao, r.total_debito, r.total_credito,
    abs(r.total_debito - r.total_credito) saldo,
    (case when r.total_debito > r.total_credito then 'D' else 'C' end) tp
from (
    select
        conta,
        sum(case when tipo='D' then valor else 0 end) total_debito, 
        sum(case when tipo='C' then valor else 0 end) total_credito,
    from (
            select 'D' tipo, conta_debito conta, valor
            from lancamento
            where not conta_debito is null
            union all
            select 'C' tipo, conta_credito, valor
            from lancamento
            where not conta_credito is null
    ) tab
    group by
        conta
) r
left join cta_contas cc on cc.conta = r.conta
GOSTEI 1
Emerson Nascimento

Emerson Nascimento

14/09/2018

Sobre filtrar por empresa, você pode colocar a empresa no agrupamento.
GOSTEI 0
Wilton Santos

Wilton Santos

14/09/2018

Muito, muito claro e objetivo. Ficou explicadissimo. Muito bom mesmo amigo, obrigado Emerson.
GOSTEI 0
Wilton Santos

Wilton Santos

14/09/2018

Na verdade eu ja tinha entendido e até tinha feito, ficou assim o meu código já adaptado para o MYSQL (obs: seu código também rodou perfeitamente no MYSQL com as clausulas CASE, WHEN - apenas fiz algumas modificações para o "if" no script do MYSQL)

Vou colocar aí para ajudar alguém mais, porque eu inclui um PERIODO POR DATA INICIAL E FINAL também usando parametros.
set@establ = 6;
set@DATAINICIO = 20150101;
set@DATAFIM = 20180917;

SELECT 
    lcto_Establ,
    p.Id as Plano,
    c.cta_nome AS Nome_da_Conta,
    conta,
    total_debito,
    total_credito,
    ABS(total_debito - total_credito) saldo,
    IF(TOTAL_DEBITO > TOTAL_CREDITO,
        'D',
        'C') Nat
FROM
    (SELECT 
        lcto_Establ,
            conta,
            SUM(IF(tipo = 'D', lcto_valor, 0)) total_Debito,
            SUM(IF(tipo = 'C', lcto_valor, 0)) total_Credito
    FROM
        (SELECT 
        'D' tipo, lcto_debito conta, lcto_valor, lcto_Establ
    FROM
        lancamento
    WHERE
        lcto_Establ = @establ
            AND NOT lcto_debito IS NULL 
            AND lcto_datamov BETWEEN @DATAINICIO AND @DATAFIM
            UNION ALL SELECT 
        'C' tipo, lcto_credito conta, lcto_valor, lcto_Establ
    FROM
        lancamento
    WHERE
        lcto_Establ = @establ
            AND NOT lcto_credito IS NULL
            AND lcto_datamov BETWEEN @DATAINICIO AND @DATAFIM) tab
    GROUP BY conta) resultado
        INNER JOIN
    planocontas p
        INNER JOIN
    cta_cuentas c
WHERE
    p.Establecimiento = lcto_establ
        AND p.id = c.cta_Plan
        AND c.cta_cod = conta
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018

recomendo fortemente que você resolva o relacionamento na própria linha de relacionamento, e não no where:
    GROUP BY conta) resultado
        INNER JOIN
             cta_cuentas c on c.cta_cod = resultado.conta
        INNER JOIN
             planocontas p on p.Establecimiento = resultado.lcto_establ
             AND p.id = c.cta_Plan

assim o where é desnecessário. esta forma de relacionamento deve melhorar a perfomance, porque ao mesmo tempo em que é feito o relacionamento, também é feita a filtragem dos registros.
(da forma como você apresentou será gerado um relacionamento do resultado x todas as contas x todo o plano de contas e, somente depois disso, será aplicado o filtro apresentado no where).
GOSTEI 1
Wilton Santos

Wilton Santos

14/09/2018

Rapaz não tinha visto isso, é verdade! obrigado! Você é o melhor!
GOSTEI 0
Wilton Santos

Wilton Santos

14/09/2018

Caro amigo, você não acharia um abuso da minha parte se te pedisse mais uma ajudinha?<br />
Gostaria de criar mais uma coluna trazendo o "Saldo Anterior" com a especificação de "C-Credor" e "D-Devedor" de determinado periodo (já que inclui as datas), antes da coluna "total debito", considerando o saldo final com a seguinte equaçâo: Saldo Anterior + Total Debito - Total Credito = Saldo.<br />
<br />
Resultado esperado:<br />
<br />
id | conta  |   saldo_anterior | tp | total_debito  |  total_credito |     saldo | tp |<br />
1  | 11110  |           200,00 | D  |       500,00  |         150,00 |    550,00 |  D |<br />
2  | 51120  |            10,00 | C  |       150,00  |         200,00 |     60,00 |  C |<br />
3  | 41105  |            50,00 | D  |         0,00  |         300,00 |    250,00 |  C |<br />
<br />
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018

tente algo assim (estou fazendo direto no bloco de notas, então pode ocorrer algum erro):

set@establ = 6;
set@DATAINICIO = 20150101;
set@DATAFIM = 20180917;

SELECT
	r.lcto_Establ,
	p.Id as Plano,
	c.cta_nome AS Nome_da_Conta,
	r.conta,
	r.saldo_Anterior,
	r.NatSA,
	r.total_debito,
	r.total_credito,
	r.saldo_Atual,
	r.Nat
FROM
	(SELECT
		lcto_Establ,
		conta,
		ABS(saldo_Anterior) saldo_Anterior,
		IF(saldo_Anterior < 0, 'D', 'C') NatSA,
		total_debito,
		total_credito,
		ABS(total_debito - total_credito) saldo_Atual,
		IF(total_debito > total_credito, 'D', 'C') Nat
	FROM
		(SELECT
			lcto_Establ,
			conta,
			SUM(IF(lcto_datamov < @DATAINICIO, lcto_valor * IF(tipo = 'D', -1, 1), 0)) saldo_Anterior,
			SUM(IF(lcto_datamov >= @DATAINICIO AND tipo = 'D', lcto_valor, 0)) total_Debito,
			SUM(IF(lcto_datamov >= @DATAINICIO AND tipo = 'C', lcto_valor, 0)) total_Credito
		FROM
			(SELECT
				lcto_datamov, 'D' tipo, lcto_debito conta, lcto_valor, lcto_Establ
			FROM
				lancamento
			WHERE
				lcto_Establ = @establ
				AND lcto_datamov <= @DATAFIM
				AND NOT lcto_debito IS NULL
			UNION ALL
			SELECT
				lcto_datamov, 'C' tipo, lcto_credito conta, lcto_valor, lcto_Establ
			FROM
				lancamento
			WHERE
				lcto_Establ = @establ
				AND lcto_datamov <= @DATAFIM
				AND NOT lcto_credito IS NULL
			) tab
		GROUP BY lcto_Establ, conta
		) resultado
	) r
INNER JOIN
	planocontas p ON p.Establecimiento = r.lcto_establ
INNER JOIN
	cta_cuentas c ON c.cta_cod = r.conta AND c.cta_Plan = p.id
GOSTEI 0
Wilton Santos

Wilton Santos

14/09/2018

Quase deu certo amigo, na verdade não tinha nenhum erro de sintaxe, o único problema que eu encontrei foi no "saldo atual" que não está batendo com a equação que apresentei, visto que o saldo anterior deve ser considerado na conta para o cálculo do saldo atual.

inclusive eu inclui o Saldo anterior lá no select do cálculo, assim:
FROM
    (SELECT
        lcto_Establ,
        conta,
        ABS(saldo_Anterior) saldo_Anterior,
        IF(saldo_Anterior < 0, 'D', 'C') NatSA,
        total_debito,
        total_credito,
        ABS(saldo_anterior  'sinal de mais' total_debito - total_credito) saldo_Atual,
        IF(total_debito > total_credito, 'D', 'C') Nat
    FROM ...

(Obs: meu "sinal de mais" nao aparece aqui no script desta plataforma do site, por isso escrevi por extenso)
mas a conta não bate, porque depende da natureza do saldo anterior, por exempo, se o Saldo anterior for credor, então o valor é negativo, se for Devedor, o valor é positivo, isto é, a equação do SALDO ATUAL deve ser: saldo_anterior 'sinal de mais' total_debito - total_credito = SALDO_ATUAL ou algo que nos traz este resultado. será que se nós retirássemos o ABS(valor absoluto) do cálculo do saldo_anterior poderiamos chegar ao resultado esperado? Eu entendi que você multiplica o valor por -1, caso o lançamento ser = "D", mas na contabilidade é exatamente o contrário, seria o "C" (valor credor) que é negativo, mas mesmo invertendo tudo aqui, e colocando o "saldo_anterior" na equação acima, não fecha, porque em algumas linhas a conta está saindo errada:
SUM(IF(lcto_datamov < @DATAINICIO, lcto_valor * IF(tipo = 'D', 1, -1), 0)) saldo_Anterior,

quando o saldo começa "C" credor por exemplo, e existe um lançamento de débito "D", ou seja, ha valores na coluna total_debito, o saldo final que aparece no meu extrato está saindo errado para todas as contas com o mesmo dilema, ou seja, observe que a linha 2 nao calcula corretamente o saldo, mas a linha 1 sim, calculou:
Establ | Plano | nome_da_conta | conta  |   saldo_anterior | tp | total_debito  |  total_credito |     saldo | tp |
   6      1	      Caja           11102	          31657.44   C	       7928.25	     17284.97	     22300.72	C
   6      1	      Energía	     51107	          7631.82    C	       2130.54	         0.00	     9762.36	D


quando a linha 2 deveria sair assim:
Establ | Plano | nome_da_conta | conta  |   saldo_anterior | tp | total_debito  |  total_credito |     saldo | tp |
   6      1	      Energía	     51107	          7631.82    C	       2130.54	         0.00	     5501.28	C

Explicando melhor, o saldo anterior é "C" Credor, logo, a subtração de 2.130,54 deve resultar em um saldo CREDOR "C", mas está somando ao saldo anterior. Comento que a soma se dá por causa da minha agregação ao código, mas sem fazer isto o resultado do "Saldo_atual" não considera o saldo anterior, apenas o mostra na tabela.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018

certo. se o resultado estiver errado, basta inverter os sinais da linha do saldo atual. e, se for o caso, pode inverter também o sinal que indica se o saldo é credor/devedor (a plataforma retira o sinal de mais, então escrevi como [mais] e [menos]).
set@establ = 6;
set@DATAINICIO = 20150101;
set@DATAFIM = 20180917;
 
SELECT
    r.lcto_Establ,
    p.Id as Plano,
    c.cta_nome AS Nome_da_Conta,
    r.conta,
    r.saldo_Anterior,
    r.NatSA,
    r.total_debito,
    r.total_credito,
    abs(r.saldo_Atual) saldo_Atual,
    IF(r.saldo_Atual < 0, 'D', 'C') Nat,
FROM
    (SELECT
        lcto_Establ,
        conta,
        abs(saldo_Anterior) saldo_Anterior,
        IF(saldo_Anterior < 0, 'D', 'C') NatSA,
        total_debito,
        total_credito,
        (saldo_Anterior [menos] total_debito [mais] total_credito) saldo_Atual
    FROM
        (SELECT
            lcto_Establ,
            conta,
            SUM(IF(lcto_datamov < @DATAINICIO, lcto_valor * IF(tipo = 'D', -1, 1), 0)) saldo_Anterior,
            SUM(IF(lcto_datamov >= @DATAINICIO AND tipo = 'D', lcto_valor, 0)) total_Debito,
            SUM(IF(lcto_datamov >= @DATAINICIO AND tipo = 'C', lcto_valor, 0)) total_Credito
        FROM
            (SELECT
                lcto_datamov, 'D' tipo, lcto_debito conta, lcto_valor, lcto_Establ
            FROM
                lancamento
            WHERE
                lcto_Establ = @establ
                AND lcto_datamov <= @DATAFIM
                AND NOT lcto_debito IS NULL
            UNION ALL
            SELECT
                lcto_datamov, 'C' tipo, lcto_credito conta, lcto_valor, lcto_Establ
            FROM
                lancamento
            WHERE
                lcto_Establ = @establ
                AND lcto_datamov <= @DATAFIM
                AND NOT lcto_credito IS NULL
            ) tab
        GROUP BY lcto_Establ, conta
        ) resultado
    ) r
INNER JOIN
    planocontas p ON p.Establecimiento = r.lcto_establ
INNER JOIN
    cta_cuentas c ON c.cta_cod = r.conta AND c.cta_Plan = p.id


GOSTEI 1
Wilton Santos

Wilton Santos

14/09/2018

Rapaz, a coisa ficou top demais. Deu certinho!
Show de Bola! Você é o melhor. Qual o curso de banco de Dados você me indicaria para ficar fera assim como você?
GOSTEI 0
Mylena

Mylena

14/09/2018

oii, estou utilizando esse comando, e me ajudou demais. Porém preciso agrupar o total de credito, total de debito e saldo por mês referente a cada conta. Exemplo:

CONTA | DATA | TOTAL DEBITO | TOTAL CREDITO | SALDO

300007 12/09/23 2000 1000 1000
300007 15/09/23 5000 2000 3000
300008 15/09/23 0000 1000 -1000

Preciso agrupar essas linhas por mês, ou seja para ser exibido que no mês 09 a conta 300007 o total de debito é 7000, total de crédito é 3000 e o saldo é 4000, E para a conta 30008 no mês 09 o total de debito é 0, total de crédito é 1000, tendo como saldo -1000.

Tentei agrupar por month (data), mas não consegui. Alguma dica de como posso fazer esse agrupamento mensal ?
GOSTEI 0
Mylena

Mylena

14/09/2018

oii, estou utilizando esse comando, e me ajudou demais. Porém preciso agrupar o total de credito, total de debito e saldo por mês referente a cada conta. Exemplo:

CONTA | DATA | TOTAL DEBITO | TOTAL CREDITO | SALDO

300007 12/09/23 2000 1000 1000
300007 15/09/23 5000 2000 3000
300008 15/09/23 0000 1000 -1000

Preciso agrupar essas linhas por mês, ou seja para ser exibido que no mês 09 a conta 300007 o total de debito é 7000, total de crédito é 3000 e o saldo é 4000, E para a conta 30008 no mês 09 o total de debito é 0, total de crédito é 1000, tendo como saldo -1000.

Tentei agrupar por month (data), mas não consegui. Alguma dica de como posso fazer esse agrupamento mensal ?
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018

no SQL Server poderia ser assim:
select
   conta,
   convert(varchar(6), campo_data, 112) mesano,
   sum(case when tipo = 'D' then valor else 0 end) debito,
   sum(case when tipo = 'C' then valor else 0 end) credito
from
  tabela
group by
   conta,
   convert(varchar(6), campo_data, 112)
GOSTEI 0
Mylena

Mylena

14/09/2018

oii, estou utilizando esse comando, e me ajudou demais. Porém preciso agrupar o total de credito, total de debito e saldo por mês referente a cada conta. Exemplo:

CONTA | DATA | TOTAL DEBITO | TOTAL CREDITO | SALDO

300007 12/09/23 2000 1000 1000
300007 15/09/23 5000 2000 3000
300008 15/09/23 0000 1000 -1000

Preciso agrupar essas linhas por mês, ou seja para ser exibido que no mês 09 a conta 300007 o total de debito é 7000, total de crédito é 3000 e o saldo é 4000, E para a conta 30008 no mês 09 o total de debito é 0, total de crédito é 1000, tendo como saldo -1000.

Tentei agrupar por month (data), mas não consegui. Alguma dica de como posso fazer esse agrupamento mensal ?
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018

você tentou a minha sugestão?
GOSTEI 0
Arthur Heinrich

Arthur Heinrich

14/09/2018

Acredito que sua dúvida original tenha sido mais que respondida. Então, vou comentar sobre o erro de modelagem.

Da forma como você implementou,

id   | empresa | data     | evento | descricao      | tipo | conta_debito | conta_credito |    valor    |  saldo
1    |   10    |  01/05   |  2     |  Rcto.NF 553   |  1   |    11110     |    41105      |  300,00     |    0
2    |   10    |  02/05   |  1     |  Pgto.NF 332   |  2   |    51120     |    11110      |  150,00     |    0
3    |   10    |  03/05   |  1     |  Devolucao     |  1   |    11110     |    51120      |  200,00     |    0


Cada lançamento só pode conter duas partidas, sendo uma a débito e outra a crédito.

Imagine que um cliente efetua um pagamento de boleto, no valor de R$ 100,00, mas o banco lhe cobra uma tarifa de R$ 3,50 pelo boleto compensado. Você deve receber na conta do banco o equivalente a R$ 96,50 e terá uma despesa de R$ 3,50 com a tarifa bancária. Para isso, você precisaria de 3 partidas, o que é impossível com o seu modelo.

Para resolver isso, você precisa de duas tabelas: Uma para lançar o documento, que contém a empresa, data, etc. A outra, conterá os itens do lançamento

lançamentos
-----------
id_lançamento
empresa
data
descrição

itens_lançamento
----------------
id_lançamento
item
dc /* débito/crédito */
conta
valor
descrição

lançamento:

id_lançamento empresa data                descrição
------------- ------- ------------------- ---------
            1    1001 05/12/2023 13:15:00 Pagamento

itens:

id_lançamento item dc conta             valor descrição
------------- ---- -- ----------------- ------ ---------
            1    1 C  cliente           100,00 Pagamento
            1    2 D  banco              96,50 Crédito de Pagamento
            1    1 D  tarifas bancárias   3,50 Tarifa de compensação de boleto


Outra coisa que não deve ser feito é colocar um saldo na conta de lançamentos.

Você pode colocar o saldo na tabela de contas, se pretende ter apenas 1 saldo. Mas, o ideal é ter uma tabela onde você possa registrar um saldo parcial. Um exemplo disso seria ter o saldo final, no último dia do mês.

Assim, para gerar um extrato, você pode ler o saldo do mês anterior, desta tabela, complementar com os itens de lançamento do mês e computar o saldo final.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

14/09/2018


Arthur Heinrich, eu acredito que ela não esteja observando o botão 'MAIS TÓPICOS', por isso não está vendo as respostas às suas dúvidas.

GOSTEI 0
POSTAR