SALDO DE DEBITO E CREDITO NO MESMO REGISTRO - MYSQL

14/09/2018

0

MySQL Banco de Dados

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

Responder

Post mais votado

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.

Emerson Nascimento

Emerson Nascimento
Responder
Gostei + 4

Mais Posts

14/09/2018

Wilton Santos

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
Responder
Gostei + 2

14/09/2018

Wilton Santos

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!
Responder
Gostei + 1

17/09/2018

Emerson Nascimento

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 |
Responder
Gostei + 1

17/09/2018

Emerson Nascimento

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
Responder
Gostei + 1

17/09/2018

Emerson Nascimento

Sobre filtrar por empresa, você pode colocar a empresa no agrupamento.
Responder
Gostei + 0

17/09/2018

Wilton Santos

Muito, muito claro e objetivo. Ficou explicadissimo. Muito bom mesmo amigo, obrigado Emerson.
Responder
Gostei + 0

17/09/2018

Wilton Santos

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
Responder
Gostei + 0

17/09/2018

Emerson Nascimento

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).
Responder
Gostei + 1

17/09/2018

Wilton Santos

Rapaz não tinha visto isso, é verdade! obrigado! Você é o melhor!
Responder
Gostei + 0

22/09/2018

Wilton Santos

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 />
Responder
Gostei + 0

24/09/2018

Emerson Nascimento

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
Responder
Gostei + 0

24/09/2018

Wilton Santos

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.
Responder
Gostei + 0

24/09/2018

Emerson Nascimento

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


Responder
Gostei + 1

25/09/2018

Wilton Santos

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ê?
Responder
Gostei + 0

05/12/2023

Mylena

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 ?
Responder
Gostei + 0
Mais tópicos

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar