Consulta SQL - Saldo devedor
Estou com a seguinte consulta:
Essa consulta está trazendo as contas a receber, porém preciso abater os lançamentos dos pagamentos já realizados.
Os pagamentos estão na tabela SUBLANCA.
Como posso filtrar e abater os pagamentos de cada titulo a receber para encontrar o saldo devedor ?
select c.CODCABRECPAG,c.DATAVCTO, c.CODTRANSACAO, c.CODUNIDCLIFOR,u.NOME, c.VALOR
from CABRECPAG C
inner join UNIDCLIFOR U on u.CODUNIDCLIFOR=c.CODUNIDCLIFOR
where c.TIPODOC='R'
and C.DATAVCTO <= '25/12/2024'
and c.EFEITO in ('A-----', 'P0----')
and c.CODFILIAL=1
and u.codgrupoclifor <> 0
order by c.CODCABRECPAG
Essa consulta está trazendo as contas a receber, porém preciso abater os lançamentos dos pagamentos já realizados.
Os pagamentos estão na tabela SUBLANCA.
Como posso filtrar e abater os pagamentos de cada titulo a receber para encontrar o saldo devedor ?
Mylena
Curtidas 0
Melhor post
Arthur Heinrich
12/12/2024
Existem diversas formas de mostrar um extrato. Porém, sua pergunta inicial é como calcular o saldo devedor.
Na teoria, quando consideramos a contabilidade, o saldo devedor de um cliente é a soma de todos os seus débitos, menos os seus créditos.
Algo do tipo:
No seu caso, existe uma tabela diferente para débitos e créditos, o que complica um pouco:
Porém, tecnicamente falando, as queries do banco precisam ser eficientes. Imagine o esforço que o seu banco faria, toda a vez que precisar do saldo de um cliente e ele se deparar com um cliente há 10 anos, com múltiplos lançamentos ao dia. Escrever um sistema assim é colocar uma data para que ele falhe, pois a cada dia que passa, o esforço aumentará, até que não seja mais capaz de entregar o resultado no tempo necessário.
O ideal é que o seu sistema contenha uma tabela de saldos, onde você possa armazenar o saldo em datas específicas. Por exemplo, no último dia do mês, permitindo que este valor seja exibido como "Saldo Anterior", sem precisar somar uma grande quantidade de dados. Daí, você soma a ele (union all), os lançamentos do mês subsequente a este saldo, limitando o seu acesso a, no máximo, 30 dias.
Outro erro comum é querer escrever uma query única para múltiplos propósitos. Isto torna a query mais complexa do que o necessário e também consome recursos.
De uma maneira geral, bancos como o Oracle e o SQL Server, possuem recursos para escrever queries analíticas.
Ao fazer isso, você consegue retornar um conjunto de dados histórico, como se fosse um extrato de conta e pode adicionar uma função analítica para, a cada linha, retornar o saldo (soma de todos os lançamentos retornados desde a primeira linha, até a linha atual.
Na teoria, quando consideramos a contabilidade, o saldo devedor de um cliente é a soma de todos os seus débitos, menos os seus créditos.
Algo do tipo:
select sum(valor) saldo from lancamentos where cliente = ?
No seu caso, existe uma tabela diferente para débitos e créditos, o que complica um pouco:
select debitos - creditos as saldo from ( select sum(valor) debitos from tab_debitos where cliente = ? ) d, ( select sum(valor) creditos from tab_creditos where cliente = ? ) c
Porém, tecnicamente falando, as queries do banco precisam ser eficientes. Imagine o esforço que o seu banco faria, toda a vez que precisar do saldo de um cliente e ele se deparar com um cliente há 10 anos, com múltiplos lançamentos ao dia. Escrever um sistema assim é colocar uma data para que ele falhe, pois a cada dia que passa, o esforço aumentará, até que não seja mais capaz de entregar o resultado no tempo necessário.
O ideal é que o seu sistema contenha uma tabela de saldos, onde você possa armazenar o saldo em datas específicas. Por exemplo, no último dia do mês, permitindo que este valor seja exibido como "Saldo Anterior", sem precisar somar uma grande quantidade de dados. Daí, você soma a ele (union all), os lançamentos do mês subsequente a este saldo, limitando o seu acesso a, no máximo, 30 dias.
Outro erro comum é querer escrever uma query única para múltiplos propósitos. Isto torna a query mais complexa do que o necessário e também consome recursos.
De uma maneira geral, bancos como o Oracle e o SQL Server, possuem recursos para escrever queries analíticas.
Ao fazer isso, você consegue retornar um conjunto de dados histórico, como se fosse um extrato de conta e pode adicionar uma função analítica para, a cada linha, retornar o saldo (soma de todos os lançamentos retornados desde a primeira linha, até a linha atual.
GOSTEI 1
Mais Respostas
Mylena
12/12/2024
Montei da seguinte forma:
Porém o resultado está exibindo titulo a titulo, como posso agrupar o total por cliente ?
Cliente Joao: 5.000,00 a receber no total, já abateu 2.000,00, então o saldo total é de 3.000,00
WITH TIT AS (SELECT CABRECPAG.CODFILIAL, CABRECPAG.CODCABRECPAG, SUBLANCA.CODFILIALRECPAG CODFILIALORIG,
SUBLANCA.CODCABRECPAG CODTITULOORIG, RENCABRECPAG.CODRENCABRECPAG, CABRECPAG.CODFILIALPARCELA,
CABRECPAG.CODTRANSACAO, CABRECPAG.PROVISORIO
FROM CABRECPAG
LEFT JOIN RENCABRECPAG ON RENCABRECPAG.CODFILIALRECPAG=CABRECPAG.CODFILIAL AND RENCABRECPAG.CODCABRECPAG=CABRECPAG.CODCABRECPAG
LEFT JOIN SUBLANCA ON (SUBLANCA.CODLANCA = RENCABRECPAG.CODLANCA)
WHERE ((CABRECPAG.TIPODOC='R')
AND CABRECPAG.CODTRANSDEV IS NULL )
AND SUBSTRING(CABRECPAG.EFEITO,1,1) <> 'N'
AND CABRECPAG.CODFILIAL=1 AND (CABRECPAG.DATAVCTO<='15/12/2024')
AND (EXISTS(SELECT UNIDCLIFOR.CODUNIDCLIFOR
FROM UNIDCLIFOR UNIDCLIFOR, CLIFOR CLIFOR WHERE (UNIDCLIFOR.CODCLIFOR=CABRECPAG.CODCLIFOR) AND
(UNIDCLIFOR.CODUNIDCLIFOR=CABRECPAG.CODUNIDCLIFOR) AND ((UNIDCLIFOR.CODGRUPOCLIFOR is not null)) AND
(CLIFOR.CODCLIFOR=UNIDCLIFOR.CODCLIFOR))) ),
TITULOS AS (SELECT C.*, NULL CODRENCABRECPAG,NULL CODVENDE, NULL NOMEVENDE, NULL CODVENDEDOR, NULL NOMEVENDEDOR, NULL CODGER, NULL NOMEGER
FROM TIT T
INNER JOIN CABRECPAG C ON C.CODFILIAL = T.CODFILIAL AND C.CODCABRECPAG = T.CODCABRECPAG)
SELECT CABRECPAG_1.DATAVCTO AS VCTO, CABRECPAG_1.DOCUMENTO, CLIFOR_1.CODCLIFOR, UNIDCLIFOR_1.CODUNIDCLIFOR,UNIDCLIFOR_1.CODTIPOCLIFOR,
(CASE WHEN(ASCII(CABRECPAG_1.TIPODOC)-ASCII('R'))=0 THEN 1 ELSE -1 END) * CABRECPAG_1.VALOR AS VALOR,
SUM(CASE WHEN SUBLANCA_1.TIPO IN ('R','P','X','Y','S') THEN SUBLANCA_1.VLRMOEDA ELSE 0 END) / (COUNT(*) / COALESCE(NULLIF(COUNT(DISTINCT SUBLANCA_1.CODSUBLANCA),0),1)) AS VALORPG,
((CASE WHEN(ASCII(CABRECPAG_1.TIPODOC)-ASCII('R'))=0 THEN 1 ELSE -1 END) * CABRECPAG_1.VALOR) - SUM(CASE WHEN SUBLANCA_1.TIPO IN ('R','P','X','Y','S') THEN SUBLANCA_1.VLRMOEDA ELSE 0 END) / (COUNT(*) / COALESCE(NULLIF(COUNT(DISTINCT SUBLANCA_1.CODSUBLANCA),0),1)) AS SALDO
FROM TITULOS CABRECPAG_1 LEFT JOIN CLIFOR CLIFOR_1 ON CLIFOR_1.CODCLIFOR=CABRECPAG_1.CODCLIFOR
LEFT JOIN UNIDCLIFOR UNIDCLIFOR_1 ON CABRECPAG_1.CODCLIFOR=UNIDCLIFOR_1.CODCLIFOR AND CABRECPAG_1.CODUNIDCLIFOR=UNIDCLIFOR_1.CODUNIDCLIFOR
LEFT JOIN SUBLANCA SUBLANCA_1 ON SUBLANCA_1.CODFILIALRECPAG=CABRECPAG_1.CODFILIAL AND SUBLANCA_1.CODCABRECPAG=CABRECPAG_1.CODCABRECPAG AND SUBLANCA_1.DATA <= '13/12/2024'
LEFT JOIN LANCA LANCA_1 ON LANCA_1.CODLANCA=SUBLANCA_1.CODLANCA
LEFT JOIN EMPRESA EMPRESA_1 ON EMPRESA_1.CODEMPRESA=CABRECPAG_1.CODFILIAL
LEFT JOIN TIPOCLIFOR TIPOCLIFOR_1 ON UNIDCLIFOR_1.CODTIPOCLIFOR=TIPOCLIFOR_1.CODTIPOCLIFOR
LEFT JOIN GRUPOCLIFOR GRUPOCLIFOR_1 ON GRUPOCLIFOR_1.CODGRUPOCLIFOR=SUBSTRING(UNIDCLIFOR_1.CODGRUPOCLIFOR,1,4)
LEFT JOIN GRUPOCLIFOR GRUPOCLIFOR_2 ON GRUPOCLIFOR_2.CODGRUPOCLIFOR=SUBSTRING(UNIDCLIFOR_1.CODGRUPOCLIFOR,1,2)
LEFT JOIN CRSITUACAO CRSITUACAO_1 ON CRSITUACAO_1.CODCRSITUACAO = CABRECPAG_1.CODCRSITUACAO
GROUP BY
CABRECPAG_1.DATAVCTO, CABRECPAG_1.DOCUMENTO, CABRECPAG_1.VALOR,UNIDCLIFOR_1.CODTIPOCLIFOR,
CLIFOR_1.CODCLIFOR, CLIFOR_1.NOME, CLIFOR_1.CODCLIFOR,
UNIDCLIFOR_1.CODUNIDCLIFOR, CABRECPAG_1.CODFILIAL,
EMPRESA_1.NOMEFILIAL, CABRECPAG_1.TIPODOC,
SUBSTRING(CABRECPAG_1.EFEITO,1,1), CABRECPAG_1.NROBLOQUETO, CABRECPAG_1.NOSSONUMERO, UNIDCLIFOR_1.CODCOBRADOR,CLIFOR_1.ECLIENTE, CLIFOR_1.EFORNECED
HAVING ((COALESCE(ROUND(SUM(CASE WHEN SUBLANCA_1.TIPO IN ('R','P','X','Y','S') THEN SUBLANCA_1.VLRMOEDA ELSE 0 END),2),0) / (COUNT(*) / COALESCE(NULLIF(COUNT(DISTINCT
SUBLANCA_1.CODSUBLANCA),0),1))) + 0.001 < ROUND(CABRECPAG_1.VALOR,2)) OR (SUM(SUBLANCA_1.VLRMOEDA) IS NULL)
ORDER BY UNIDCLIFOR_1.CODUNIDCLIFOR
Porém o resultado está exibindo titulo a titulo, como posso agrupar o total por cliente ?
Cliente Joao: 5.000,00 a receber no total, já abateu 2.000,00, então o saldo total é de 3.000,00
GOSTEI 0
Arthur Heinrich
12/12/2024
Sua query está muito complexa para entender o que é relevante ou não para este resultado. Aparentemente há uma checagem, título a título, para considerar o título quitado e excluí-lo da relação. Então, não está fácil simplificar a sua query, mas dá para adicionar mais uma parte, para simplificar o resultado final.
with
tit as
( select
cabrecpag.codfilial, cabrecpag.codcabrecpag, sublanca.codfilialrecpag codfilialorig,
sublanca.codcabrecpag codtituloorig, rencabrecpag.codrencabrecpag, cabrecpag.codfilialparcela,
cabrecpag.codtransacao, cabrecpag.provisorio
from
cabrecpag
left join rencabrecpag
on rencabrecpag.codfilialrecpag=cabrecpag.codfilial and
rencabrecpag.codcabrecpag=cabrecpag.codcabrecpag
left join sublanca
on sublanca.codlanca = rencabrecpag.codlanca
where
( cabrecpag.tipodoc='R' and
cabrecpag.codtransdev is null ) and
substring(cabrecpag.efeito,1,1) <> 'N' and
cabrecpag.codfilial = 1 and
cabrecpag.datavcto <= '15/12/2024' and
( exists( select unidclifor.codunidclifor
from
unidclifor unidclifor,
clifor clifor
where
(unidclifor.codclifor=cabrecpag.codclifor) and
(unidclifor.codunidclifor=cabrecpag.codunidclifor) and
(unidclifor.codgrupoclifor is not null) and
(clifor.codclifor=unidclifor.codclifor) ) ) ),
titulos as
( select
c.*, null codrencabrecpag, null codvende, null nomevende, null codvendedor,
null nomevendedor, null codger, null nomeger
from
tit t
inner join cabrecpag c
on c.codfilial = t.codfilial and
c.codcabrecpag = t.codcabrecpag),
lanc_indiv as
( select
clifor_1.nome,
(case when cabrecpag_1.tipodoc = 'R' then 1 else -1 end) * cabrecpag_1.valor as debito,
sum(case when sublanca_1.tipo in ('R','P','X','Y','S') then sublanca_1.vlrmoeda else 0 end)/
(count(*) / coalesce(nullif(count(distinct sublanca_1.codsublanca),0),1)) as valorpg
from
titulos cabrecpag_1
left join clifor clifor_1
on clifor_1.codclifor=cabrecpag_1.codclifor
left join unidclifor unidclifor_1
on cabrecpag_1.codclifor=unidclifor_1.codclifor and
cabrecpag_1.codunidclifor=unidclifor_1.codunidclifor
left join sublanca sublanca_1
on sublanca_1.codfilialrecpag=cabrecpag_1.codfilial and
sublanca_1.codcabrecpag=cabrecpag_1.codcabrecpag and sublanca_1.data <= '13/12/2024'
left join lanca lanca_1
on lanca_1.codlanca=sublanca_1.codlanca
left join empresa empresa_1
on empresa_1.codempresa=cabrecpag_1.codfilial
left join tipoclifor tipoclifor_1
on unidclifor_1.codtipoclifor=tipoclifor_1.codtipoclifor
left join grupoclifor grupoclifor_1
on grupoclifor_1.codgrupoclifor=substring(unidclifor_1.codgrupoclifor,1,4)
left join grupoclifor grupoclifor_2
on grupoclifor_2.codgrupoclifor=substring(unidclifor_1.codgrupoclifor,1,2)
left join crsituacao crsituacao_1
on crsituacao_1.codcrsituacao = cabrecpag_1.codcrsituacao
group by
cabrecpag_1.datavcto, cabrecpag_1.documento, cabrecpag_1.valor,unidclifor_1.codtipoclifor,
clifor_1.codclifor, clifor_1.nome, clifor_1.codclifor,
unidclifor_1.codunidclifor, cabrecpag_1.codfilial,
empresa_1.nomefilial, cabrecpag_1.tipodoc,
substring(cabrecpag_1.efeito,1,1), cabrecpag_1.nrobloqueto, cabrecpag_1.nossonumero,
unidclifor_1.codcobrador,clifor_1.ecliente, clifor_1.eforneced
having
((coalesce(round(sum(case when sublanca_1.tipo in ('R','P','X','Y','S') then sublanca_1.vlrmoeda else 0 end),2),0)/
(count(*) / coalesce(nullif(count(distinctsublanca_1.codsublanca),0),1))) + 0.001 < round(cabrecpag_1.valor,2)) or (sum(sublanca_1.vlrmoeda) is null) )
select
nome,
sum(debito) debito,
sum(valorpg) pago,
sum(debito) - sum(valorpg) saldo
from lanc_indiv
group by nome
order by 1
GOSTEI 1
Mylena
12/12/2024
Deu certinho, muito obrigada
GOSTEI 0