[Duvida]Sql Server Consulta em Select
19/10/2023
0
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
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
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
Curtir tópico
+ 0
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.
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.
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
Responder
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
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.
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.
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
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.
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.
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
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
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.
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
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.
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
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.
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
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.
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
Clique aqui para fazer login e interagir na Comunidade :)
