SELECT + SUM + FILTER

[Cezar Pereira (Categero) 10]

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 

 

Edited November 7, 2019 by Cezar

[JULIO CESAR PEDROSO 96]

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.

 

[Cezar Pereira (Categero) 10]

Para falar a verdade não, views são mais um horizonte para mim aprender.

Valeu pela dica 

[FABRÍCIO 134]

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))

[Evandro Andersen 58]

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

 

[Cezar Pereira (Categero) 10]

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

 

[Evandro Andersen 58]

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.

[Willian Fernando Padilha 15]

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.

[Mello Junior 63]

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

 

[Cezar Pereira (Categero) 10]

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!

 

 

[Cleiton Fidelis 45]

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

Valeu demais....

 

[Willian Fernando Padilha 15]

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