[Duvida]Sql Server Consulta em Select

19/10/2023

0

SQL Server SELECT

Olá estimados amigos, estou precisando de uma ajuda

no sql server tenho uma tabela chamada cliente
com as seguinte colunas
dt , Credito , Debito

Nesta tabela eu tenho os seguintes registros ao executar o comando select * from cliente

Dt Credito Debito
01/10/2023 - 25,00
02/10/2023 - 10,00
03/10/2023 20,00 -
04/10/2023 10,00 10,00
05/10/2023 15,00 -
06/10/2023 35,00 -
07/10/2023 30,00 45,00
08/10/2023 - 5,00
09/10/2023 30,00 -
10/10/2023 - 15,00
11/10/2023 15,00 50,00
12/10/2023 12,00 5,00
13/10/2023 - -
14/10/2023 25,00 10,00
15/10/2023 15,00 10,00
16/10/2023 25,00 -
17/10/2023 20,00 60,00



o que estou precisando é fazer duas colunas run-time obedecento uma data de corte
1º coluna chamara SaldoAnterior
Atravez dessa data de corte em um where ,
o resultado da primeira linha Coluna SaldoAnterior deverá trazer o seguinte resultado
(soma total credito) + (soma total dédito *-1) = SaldoAnterior a data de corte
já nas linha segunda para frente a conta muda , o SaldoAnterior das lnhas seguintes seria (SaldoAtual da linha anterior + credito linha atual + (debito linha atual * -1))
e assim sucessivamente
2º coluna chamara SaldoAtual
já nesta segunda coluna de SaldoAtual a conta seria (SaldoAnterior + Credito da linha atual + (Debito da linha atual *-1))
Exemplo
trazendo assim sem passar nenhuma data de corte
Dt SALDOANTERIOR Credito Debito SALDOATUAL
01/10/2023 - - 25,00 -25,00
02/10/2023 -25,00 - 10,00 -35,00
03/10/2023 -35,00 20,00 - -15,00
04/10/2023 -15,00 10,00 10,00 -15,00
05/10/2023 -15,00 15,00 - -
06/10/2023 - 35,00 - 35,00
07/10/2023 35,00 30,00 45,00 20,00
08/10/2023 20,00 - 5,00 15,00
09/10/2023 15,00 30,00 - 45,00
10/10/2023 45,00 - 15,00 30,00
11/10/2023 30,00 15,00 50,00 -5,00
12/10/2023 -5,00 12,00 5,00 2,00
13/10/2023 2,00 - - 2,00
14/10/2023 2,00 25,00 10,00 17,00
15/10/2023 17,00 15,00 10,00 22,00
16/10/2023 22,00 25,00 - 47,00
17/10/2023 47,00 20,00 60,00 7,00

porém a data do where será dia 04-10-2023
então o resultado que estou buscando seria o abaixo

Dt SALDOANTERIOR Credito Debito SALDOATUAL
04/10/2023 -15,00 10,00 10,00 -15
05/10/2023 -15,00 15,00 - 0
06/10/2023 - 35,00 - 35
07/10/2023 35,00 30,00 45,00 20
08/10/2023 20,00 - 5,00 15
09/10/2023 15,00 30,00 - 45
10/10/2023 45,00 - 15,00 30
11/10/2023 30,00 15,00 50,00 -5
12/10/2023 -5,00 12,00 5,00 2
13/10/2023 2,00 - - 2
14/10/2023 2,00 25,00 10,00 17
15/10/2023 17,00 15,00 10 22
16/10/2023 22,00 25,00 0 47
17/10/2023 47,00 20,00 60 7

estou tentando assim com o select abaixo , mas esta dando errado na parte do union all


DECLARE @DATA DATE = '2023-10-01';

SELECT 
    DT
    ,(SELECT ISNULL(SUM(DEBITO) * -1 + SUM(CREDITO),0) AS SALDOANTERIOR FROM CLIENTE WHERE DT < @DATA) AS SALDOANTERIOR
    ,(SELECT SUM(CREDITO) AS CREDITO FROM CLIENTE WHERE DT = @DATA) AS CREDITO
	,(SELECT SUM(DEBITO) * - 1 AS DEBITO FROM CLIENTE WHERE DT = @DATA) AS DEBITO
	,(SELECT (SELECT ISNULL(SUM(DEBITO) * -1 + SUM(CREDITO),0) AS SALDOANTERIOR FROM CLIENTE WHERE DT < @DATA) 
	   + (SELECT SUM(CREDITO) AS CREDITO FROM CLIENTE WHERE DT = @DATA) 
	   + (SELECT SUM(DEBITO) * -1 AS DEBITO FROM CLIENTE WHERE DT = @DATA) FROM CLIENTE WHERE DT = @DATA) AS SALDOATUAL
    FROM cliente
	WHERE DT = @DATA
group by DT,CREDITO,DEBITO

UNION ALL

SELECT 
    DT
    ,LAG(SALDOANTERIOR,1,0) OVER (PARTITION BY DT ORDER BY DT) AS SALDOANTERIOR
    ,CREDITO AS CREDITO
	,DEBITO AS DEBITO
	,(SELECT (SELECT ISNULL(SUM(DEBITO),0) * -1 + ISNULL(SUM(CREDITO),0)) AS SALDOANTERIOR FROM CLIENTE WHERE DT < DATEADD(DAY,1,@DATA)) + CREDITO + DEBITO AS SALDOATUAL
    FROM cliente
	WHERE DT > @DATA
group by DT,CREDITO,DEBITO


não sei que esse é o melhor caminho a seguir para fazer esse conta correto (semelhante ao extrato de banco)... por favor , se puderem me ajudar agradeço grandemente por isso
Mauricio Gabriel

Mauricio Gabriel

Responder

Post mais votado

19/10/2023

Em primeiro lugar, se você pretende utilizar esta estratégia em um sistema de verdade, já adianto que é a maneira errada de manter um extrato de cliente. Toda vez que você efetua a consulta, o banco teria que ler todos os dados desde o início, o que é muito ruim.

Porém, você pode utilizar funções analíticas para isso.

select
  dt, saldo_anterior, credito, debito, saldo_atual
from
  ( select
      dt,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )-credito+debito 
saldo_anterior,
      credito,
      debito,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) saldo_atual
    from cliente ) dados
where
  dt >= @DATA


Porém, para que funcione corretamente, o order by da cláusula over() precisa ordenar os dados de forma consistente. Utilizando apenas a data, se houver mais de um valor por data, os dados podem ser considerados na ordem incorreta. O ideal seria ordenar por uma data e número sequencial de transação.

Arthur Heinrich

Arthur Heinrich
Responder
Gostei + 1

Mais Posts

19/10/2023

Emerson Nascimento

tente isto:
DECLARE @DATA DATE = '2023-10-01';

SELECT
	T.DT,
	T.SALDOANTERIOR,
	T.CREDITO,
	T.DEBITO,
	(COALESCE(T.SALDOANTERIOR,0) + T.CREDITO - T.DEBITO) SALDOATUAL
FROM (
	SELECT
		C1.DT
    	,(SELECT COALESCE(SUM(C2.CREDITO),0) - COALESCE(SUM(C2.DEBITO),0) FROM CLIENTE C2 WHERE /*C2.CLIENTE = C1.CLIENTE AND*/ C2.DT < C1.DT) SALDOANTERIOR,
    	,SUM(C1.CREDITO) CREDITO
    	,SUM(C1.DEBITO) DEBITO
	FROM
    	CLIENTE C1
	WHERE
--		C1.CLIENTE = @CODCLI AND
		C1.DT >= @DATA
	GROUP BY
		C1.DT
) T
ORDER BY
	T.DT


Responder
Gostei + 1

20/10/2023

Mauricio Gabriel

Em primeiro lugar, se você pretende utilizar esta estratégia em um sistema de verdade, já adianto que é a maneira errada de manter um extrato de cliente. Toda vez que você efetua a consulta, o banco teria que ler todos os dados desde o início, o que é muito ruim.

Porém, você pode utilizar funções analíticas para isso.

select
  dt, saldo_anterior, credito, debito, saldo_atual
from
  ( select
      dt,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )-credito+debito 
saldo_anterior,
      credito,
      debito,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) saldo_atual
    from cliente ) dados
where
  dt >= @DATA


Porém, para que funcione corretamente, o order by da cláusula over() precisa ordenar os dados de forma consistente. Utilizando apenas a data, se houver mais de um valor por data, os dados podem ser considerados na ordem incorreta. O ideal seria ordenar por uma data e número sequencial de transação.
Responder
Gostei + 0

20/10/2023

Mauricio Gabriel

Em primeiro lugar, se você pretende utilizar esta estratégia em um sistema de verdade, já adianto que é a maneira errada de manter um extrato de cliente. Toda vez que você efetua a consulta, o banco teria que ler todos os dados desde o início, o que é muito ruim.

Porém, você pode utilizar funções analíticas para isso.

select
  dt, saldo_anterior, credito, debito, saldo_atual
from
  ( select
      dt,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )-credito+debito 
saldo_anterior,
      credito,
      debito,
      sum(coalesce(credito,0)-coalesce(debito,0)) over(order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) saldo_atual
    from cliente ) dados
where
  dt >= @DATA


Porém, para que funcione corretamente, o order by da cláusula over() precisa ordenar os dados de forma consistente. Utilizando apenas a data, se houver mais de um valor por data, os dados podem ser considerados na ordem incorreta. O ideal seria ordenar por uma data e número sequencial de transação.




olá
Arthur Heinrich
excelente sua dia , acabei de fazer o teste e era realmente o que estava buscando ...
Sobre sua sugestão de percorrer todo o banco , vou levar em consideração é uma dica muito válida ...
Antecipo cordial agradecimento pela ajuda prestada.
Responder
Gostei + 0

20/10/2023

Mauricio Gabriel

tente isto:
DECLARE @DATA DATE = '2023-10-01';

SELECT
	T.DT,
	T.SALDOANTERIOR,
	T.CREDITO,
	T.DEBITO,
	(COALESCE(T.SALDOANTERIOR,0) + T.CREDITO - T.DEBITO) SALDOATUAL
FROM (
	SELECT
		C1.DT
    	,(SELECT COALESCE(SUM(C2.CREDITO),0) - COALESCE(SUM(C2.DEBITO),0) FROM CLIENTE C2 WHERE /*C2.CLIENTE = C1.CLIENTE AND*/ C2.DT < C1.DT) SALDOANTERIOR,
    	,SUM(C1.CREDITO) CREDITO
    	,SUM(C1.DEBITO) DEBITO
	FROM
    	CLIENTE C1
	WHERE
--		C1.CLIENTE = @CODCLI AND
		C1.DT >= @DATA
	GROUP BY
		C1.DT
) T
ORDER BY
	T.DT






olá
Emerson Nascimento
quero agradecer também a sua resposta, pois atendeu também minha necessidade ... aprendi uma outra forma de fazer para chegar em um resultado.
Agradeço por compartilhar conhecimento , onde serei replicador deste a outros que comigo trabalham.
Antecipo cordial agradecimento pela ajuda prestada.
Responder
Gostei + 0

20/10/2023

Arthur Heinrich

olá
Arthur Heinrich
excelente sua dia , acabei de fazer o teste e era realmente o que estava buscando ...
Sobre sua sugestão de percorrer todo o banco , vou levar em consideração é uma dica muito válida ...
Antecipo cordial agradecimento pela ajuda prestada.


Para tornar a consulta eficiente, você não pode calcular o saldo anterior somando todos os lançamentos, desde o início.

Um artifício para isso é calcular saldos em datas específicas. Por exemplo, você pode ter uma tabela que armazene o saldo no último dia do mês, por exemplo.

Ela seria populada com (cliente, conta, data, saldo) ou coisa do tipo.

Para exibir o extrato, você descobre a maior data na tabela de saldos e, então, retorna os dados individuais a partir da data do saldo encontrado. Desta forma, para gerar um extrato de 1 mês, você só precisa ler 1 registro do saldo (saldo anterior), e buscar dados do mês, mesmo que o cliente tenha mais de 10 anos de dados.

Ao fazer isso, o esforço que sua aplicação tem que fazer para retornar os dados se torna incremental e torna a eficiência estável ao longo do tempo.
Responder
Gostei + 1

23/10/2023

Emerson Nascimento

Complementando o que o colega Arthur Heinrich, a geração dessas tabelas de acumulados é chamada normalmente de rotinas de fechamento.

Você pode ter fechamento:
- contábil
- fiscal
- financeiro
- estoque
o fechamento pode ser na data que o usuário desejar, mas pode ser criada uma regra de periodicidade, a depender do tipo de fechamento. uma vez que o fechamento é concluído, o sistema não pode permitir movimentação retroativa (precisa prestar atenção nas rotinas "integradas").

a partir daí você pega o fechamento imediatamente anterior à data que você precisa para poder compor o saldo anterior.
Responder
Gostei + 1

27/10/2023

Mauricio Gabriel

olá
Arthur Heinrich
excelente sua dia , acabei de fazer o teste e era realmente o que estava buscando ...
Sobre sua sugestão de percorrer todo o banco , vou levar em consideração é uma dica muito válida ...
Antecipo cordial agradecimento pela ajuda prestada.


Para tornar a consulta eficiente, você não pode calcular o saldo anterior somando todos os lançamentos, desde o início.

Um artifício para isso é calcular saldos em datas específicas. Por exemplo, você pode ter uma tabela que armazene o saldo no último dia do mês, por exemplo.

Ela seria populada com (cliente, conta, data, saldo) ou coisa do tipo.

Para exibir o extrato, você descobre a maior data na tabela de saldos e, então, retorna os dados individuais a partir da data do saldo encontrado. Desta forma, para gerar um extrato de 1 mês, você só precisa ler 1 registro do saldo (saldo anterior), e buscar dados do mês, mesmo que o cliente tenha mais de 10 anos de dados.

Ao fazer isso, o esforço que sua aplicação tem que fazer para retornar os dados se torna incremental e torna a eficiência estável ao longo do tempo.



Muito bom dia !!! Vou levar em consideração esta lógica ... Muito obrigado por compartilhar comigo conhecimento e boas práticas ...
Responder
Gostei + 0

27/10/2023

Mauricio Gabriel

Complementando o que o colega Arthur Heinrich, a geração dessas tabelas de acumulados é chamada normalmente de rotinas de fechamento.

Você pode ter fechamento:
- contábil
- fiscal
- financeiro
- estoque
o fechamento pode ser na data que o usuário desejar, mas pode ser criada uma regra de periodicidade, a depender do tipo de fechamento. uma vez que o fechamento é concluído, o sistema não pode permitir movimentação retroativa (precisa prestar atenção nas rotinas "integradas").

a partir daí você pega o fechamento imediatamente anterior à data que você precisa para poder compor o saldo anterior.



Muito bom dia !!!
Excelente comentário e dica , já estou colocando em prática também ...
Agrandeço grandemente pela ajuda prestada.
Responder
Gostei + 0

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

Aceitar