Cezar Pereira (Categero)

SELECT + SUM + FILTER

12 posts in this topic

Bom dia Pessoal,

Estou trabalhando numa select onde preciso pegar a somatória da diferença entre entradas e saída para apresentar só o saldo.

O campo da quantidade (notasaida_itens.notsaip_qtdade) é sempre positivo, o seu tratamento depende de uma coluna de outra tabela (cfop.cfop_operacao) que indica se a operação é de uma entrada ou saída, portanto, o valor de todas as linhas da coluna de quantidade são positivos, esse é o cenário que me deixaram e não tenho como ajustar a coluna no banco.

Segue o código SQL, onde tenho um SUM da notasaida_itens.notsaip_qtdade quando cfop.cfop_operacao  = '+' e outro SUM quando cfop.cfop_operacao = '-', mas preciso apresentar somente o saldo, usando o SQL

SELECT 
notasaida_itens.notsaip_cod_empresa AS notsaip_cod_empresa,    
notasaida_itens.notsaip_cod_produto AS notsaip_cod_produto,    
notasaida_itens.notsaip_cod_cliente AS notsaip_cod_cliente,    
notasaida_itens.notsaip_descricao AS notsaip_descricao,    
cfop.cfop_operacao AS cfop_operacao,    
notasaida_itens.notsaip_dt_emis AS notsaip_dt_emis,    
notasaida.notsai_dt_cancelamento AS notsai_dt_cancelamento,
SUM(notasaida_itens.notsaip_qtdade) filter (WHERE cfop.cfop_operacao = '+') AS saida,
SUM(notasaida_itens.notsaip_qtdade) filter (WHERE cfop.cfop_operacao = '-') AS entrada
FROM 
notasaida_itens,    
cfop,    
notasaida
WHERE 
notasaida_itens.notsaip_cfop = cfop.cfop_codigo
AND        notasaida_itens.notsaip_cod_empresa = notasaida.notsai_cod_empresa
AND    notasaida_itens.notsaip_modelo_nf = notasaida.notsai_modelo_nf
AND    notasaida_itens.notsaip_serie_nf = notasaida.notsai_serie_nf
AND    notasaida_itens.notsaip_notafiscal = notasaida.notsai_notafiscal
AND
(
notasaida_itens.notsaip_cod_empresa = {param_codempresa}
AND    notasaida_itens.notsaip_dt_emis = {Paramnotsai_dt_emissao}
AND    cfop.cfop_operacao IN ('+', '-') 
AND    notasaida.notsai_dt_cancelamento IS NULL 
)
GROUP BY 
    notasaida_itens.notsaip_cod_empresa, 
    notasaida_itens.notsaip_cod_cliente, 
    notasaida_itens.notsaip_cod_produto,
    notasaida_itens.notsaip_descricao, 
    notasaida_itens.notsaip_dt_emis, 
    cfop.cfop_operacao, 
    notasaida.notsai_dt_cancelamento
ORDER BY notasaida_itens.notsaip_cod_cliente
 

O resultado dessa query trouxe duas linhas:

emp|prod                   |codcli    |nome produto                                            |oper|Data venda  |Dt,Canc|Saida  |Entrada| 

2    2007                    57503    CHUPETA CRISTAL COLOR 1 AZ.MAR.     -    31/10/2019    NULL    NULL    6,00

2    2007                    57503    CHUPETA CRISTAL COLOR 1 AZ.MAR.     +    31/10/2019    NULL    12,00    NULL

Eu preciso apresentar somente uma linha com saldo, se alguém puder me ajudar eu agradeço :D

 

Edited by Cezar

Share this post


Link to post
Share on other sites

Cézar, já pensou em usar Views para fazer isto?

Eu tenho uma demanda deste tipo.

Neste caso eu crio uma View para as entradas e outra para as saídas (onde obtenho as somas)

depois só subtraio uma view de outra para obter o saldo.

Desculpe pela simplicidade da resposta.

 

3 people like this

Share this post


Link to post
Share on other sites

Cezar.. faça 2 subquerys depois você pega os 2  resultados
e soma.. a questão do valor nulo.. basta usar o operador NVL ou COALESCE
exemplo:
SUM(COALESCE(notasaida_itens.notsaip_qtdade,0))

3 people like this

Share this post


Link to post
Share on other sites

Bom dia Cezar,

não sei qual banco de dados utilizando,
mas no postgresql você poderia calcular a coluna do saldo sem a necessidade de gerar 2 colunas (entradas e saídas)

ficaria assim o comando SUM:

sum(case when cfop.cfop_operacao = '+' then notasaida_itens.notsaip_qtdade else notasaida_itens.notsaip_qtdade * -1 end) as SALDO

Traduzinho:
se o campo cfop_operacao for "+", ele soma o campo notsaip_qtdade, se não ele soma o notsaip_qtdade * -1

simples assim

Att,

Evandro Andersen
HDTec Software

 

4 people like this

Share this post


Link to post
Share on other sites
2 minutos atrás, evandro disse:

Bom dia Cezar,

não sei qual banco de dados utilizando,
mas no postgresql você poderia calcular a coluna do saldo sem a necessidade de gerar 2 colunas (entradas e saídas)

ficaria assim o comando SUM:

sum(case when cfop.cfop_operacao = '+' then notasaida_itens.notsaip_qtdade else notasaida_itens.notsaip_qtdade * -1 end) as SALDO

Traduzinho:
se o campo cfop_operacao for "+", ele soma o campo notsaip_qtdade, se não ele soma o notsaip_qtdade * -1

simples assim

Att,

Evandro Andersen
HDTec Software

 

bom dia Evandro, tudo bom meu amigo?

Estou usando o PG para esse trabalho, tendei fazer algo semelhante e não tive muito sucesso, mas vou tentar de novo com o teu conselho, acho que com as 2 xícaras de café que acabei de tomar, talvez o tico e teco despertem :)

 

Share this post


Link to post
Share on other sites
2 minutos atrás, Cezar disse:

bom dia Evandro, tudo bom meu amigo?

Estou usando o PG para esse trabalho, tendei fazer algo semelhante e não tive muito sucesso, mas vou tentar de novo com o teu conselho, acho que com as 2 xícaras de café que acabei de tomar, talvez o tico e teco despertem :)

 

Eu uso essa mesma lógica em vários SQL do meu sistema, caso não consiga, me avise que te ajudo.

1 person likes this

Share this post


Link to post
Share on other sites

Tenta assim (o bom era si tivesse dados de exemplo apenas com a query fica dificil entender como esta as tabelas e dados)

P.D: tente usar as TAG CODE para incluir codigos (sql, wls, etc)

 

SELECT 
notasaida_itens.notsaip_cod_empresa AS notsaip_cod_empresa,    
notasaida_itens.notsaip_cod_produto AS notsaip_cod_produto,    
notasaida_itens.notsaip_cod_cliente AS notsaip_cod_cliente,    
notasaida_itens.notsaip_descricao AS notsaip_descricao,      
notasaida_itens.notsaip_dt_emis AS notsaip_dt_emis,    
notasaida.notsai_dt_cancelamento AS notsai_dt_cancelamento,
SUM( CASE cfop.cfop_operacao WHEN '+' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS saida,
SUM( CASE cfop.cfop_operacao WHEN '-' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS entrada,
SUM( CASE cfop.cfop_operacao WHEN '-' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) - SUM( CASE cfop.cfop_operacao WHEN '+' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS saldo
FROM 
notasaida_itens,    
cfop,    
notasaida
WHERE 
notasaida_itens.notsaip_cfop = cfop.cfop_codigo
AND        notasaida_itens.notsaip_cod_empresa = notasaida.notsai_cod_empresa
AND    notasaida_itens.notsaip_modelo_nf = notasaida.notsai_modelo_nf
AND    notasaida_itens.notsaip_serie_nf = notasaida.notsai_serie_nf
AND    notasaida_itens.notsaip_notafiscal = notasaida.notsai_notafiscal
AND
(
notasaida_itens.notsaip_cod_empresa = {param_codempresa}
AND    notasaida_itens.notsaip_dt_emis = {Paramnotsai_dt_emissao}
AND    cfop.cfop_operacao IN ('+', '-') 
AND    notasaida.notsai_dt_cancelamento IS NULL 
)
GROUP BY 
    notasaida_itens.notsaip_cod_empresa AS notsaip_cod_empresa,    
	notasaida_itens.notsaip_cod_produto AS notsaip_cod_produto,    
	notasaida_itens.notsaip_cod_cliente AS notsaip_cod_cliente,    
	notasaida_itens.notsaip_descricao AS notsaip_descricao,    
	notasaida_itens.notsaip_dt_emis AS notsaip_dt_emis,    
	notasaida.notsai_dt_cancelamento AS notsai_dt_cancelamento
ORDER BY notasaida_itens.notsaip_cod_cliente

OBSERVAÇÃO

tu não pode agrupar pelo valor ou condição que vai ser sumado, pois vai repetir mesmo as linhas

fiquei em duvida quanto as datas , mas em fim, teste e nos diga o resultado.

1 person likes this

Share this post


Link to post
Share on other sites

Cezar,

Como o próprio Willian indicou, funções de agregação exigem o group by, mas elas tem de acompanhar todas as colunas da seção SELECT que não são de agregação dentro do GROUP BY. Além do que uma coluna condicional deve estar SEMPRE com o elemento CASE que é a cláusula adequada para filtrar os casos corretamente.

Att

 

2 people like this

Share this post


Link to post
Share on other sites
2 horas atrás, Willian Fernando Padilha disse:

Tenta assim (o bom era si tivesse dados de exemplo apenas com a query fica dificil entender como esta as tabelas e dados)

P.D: tente usar as TAG CODE para incluir codigos (sql, wls, etc)

 



SELECT 

notasaida_itens.notsaip_cod_empresa AS notsaip_cod_empresa,    

notasaida_itens.notsaip_cod_produto AS notsaip_cod_produto,    

notasaida_itens.notsaip_cod_cliente AS notsaip_cod_cliente,    

notasaida_itens.notsaip_descricao AS notsaip_descricao,      

notasaida_itens.notsaip_dt_emis AS notsaip_dt_emis,    

notasaida.notsai_dt_cancelamento AS notsai_dt_cancelamento,

SUM( CASE cfop.cfop_operacao WHEN '+' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS saida,

SUM( CASE cfop.cfop_operacao WHEN '-' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS entrada,

SUM( CASE cfop.cfop_operacao WHEN '-' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) - SUM( CASE cfop.cfop_operacao WHEN '+' THEN  notasaida_itens.notsaip_qtdade ELSE 0 END) AS saldo

FROM 

notasaida_itens,    

cfop,    

notasaida

WHERE 

notasaida_itens.notsaip_cfop = cfop.cfop_codigo

AND        notasaida_itens.notsaip_cod_empresa = notasaida.notsai_cod_empresa

AND    notasaida_itens.notsaip_modelo_nf = notasaida.notsai_modelo_nf

AND    notasaida_itens.notsaip_serie_nf = notasaida.notsai_serie_nf

AND    notasaida_itens.notsaip_notafiscal = notasaida.notsai_notafiscal

AND

(

notasaida_itens.notsaip_cod_empresa = {param_codempresa}

AND    notasaida_itens.notsaip_dt_emis = {Paramnotsai_dt_emissao}

AND    cfop.cfop_operacao IN ('+', '-') 

AND    notasaida.notsai_dt_cancelamento IS NULL 

)

GROUP BY 

    notasaida_itens.notsaip_cod_empresa AS notsaip_cod_empresa,    

	notasaida_itens.notsaip_cod_produto AS notsaip_cod_produto,    

	notasaida_itens.notsaip_cod_cliente AS notsaip_cod_cliente,    

	notasaida_itens.notsaip_descricao AS notsaip_descricao,    

	notasaida_itens.notsaip_dt_emis AS notsaip_dt_emis,    

	notasaida.notsai_dt_cancelamento AS notsai_dt_cancelamento

ORDER BY notasaida_itens.notsaip_cod_cliente

OBSERVAÇÃO

tu não pode agrupar pelo valor ou condição que vai ser sumado, pois vai repetir mesmo as linhas

fiquei em duvida quanto as datas , mas em fim, teste e nos diga o resultado.

Willian, acertou na mosca :)

o único ajuste for retirar os alias do group by

GROUP BY 
notasaida_itens.notsaip_cod_empresa,    
notasaida_itens.notsaip_cod_produto,    
notasaida_itens.notsaip_cod_cliente,    
notasaida_itens.notsaip_descricao,    
notasaida_itens.notsaip_dt_emis,    
notasaida.notsai_dt_cancelamento

Rodou certinho no pgadmin e no teste da query no windev, show de bola! Tenho muito para aprender :)

Muito obrigado a você Willian e aos amigos Evandro Andersen, Fabricio, Mello e Pedroso, ... agradecimento por ordem alfabética para não entregar a idade de ninguém....rsrsrsrsrs

E parabéns ao fórum!

 

 

Share this post


Link to post
Share on other sites

Caramba, só respostas de auto padrão...
Babando aqui..

Valeu demais....

 

2 people like this

Share this post


Link to post
Share on other sites
1 hora atrás, Cezar Pereira (Categero) disse:

o único ajuste for retirar os alias do group by

ahh sim, foi mal, é que peguei das colunas de cima, e esqueci de remover os alias. show de bola

1 person likes this

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now