 |
ActiveDelphi
.: O site do programador Delphi! :.
|
| Exibir mensagem anterior :: Exibir próxima mensagem |
| Autor |
Mensagem |
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
 Enviada: Qua Nov 22, 2017 8:43 am Assunto: [Resolvido] Ajuda - Erro SQL geração de estoque |
 |
|
Salve, amigos.
Tenho as duas tabelas abaixo:
Tabela Entrada_produto
SEQUENCIA
ID_GRUPO_PRODUTO
ID_SUB_GRUPO_PRODUTO
CODIGO_PRODUTO
DATA_FABRICACAO
QUANTIDADE
ID_UNIDADE_PRODUTO
LOTE
ENVASE
DATA_VALIDADE
DATA_ENTRADA
ID_EMPRESA
OBSERVACAO
Tabela Saida_produto
SEQUENCIA
ID_GRUPO_PRODUTO
ID_SUB_GRUPO_PRODUTO
CODIGO_PRODUTO
DATA_FABRICACAO
QUANTIDADE
ID_UNIDADE_PRODUTO
LOTE
ENVASE
DATA_VALIDADE
DATA_SAIDA
ID_EMPRESA
OBSERVACAO
Estou tentando gerar um Estoque com elas. Fiz a SQL abaixo para resgatar as informações que preciso mas está dando erro. Eu preciso que as informações não saiam repetidas pois a SQl também servirá para o futuro relatório.
SQL:
select distinct
ep1.id_grupo_produto,
grupos_produtos.nome_grupo,
ep1.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
ep1.codigo_produto,
produtos.nome,
ep1.data_fabricacao,
(select (sum(ep2.quantidade) - sum(saida_produto.quantidade))
from entrada_produto as ep2
left join saida_produto on
((ep.codigo_produto = saida_produto.codigo_produto) and (ep2.lote = saida_produto.lote))
where ((ep2.codigo_produto = ep1.codigo_produto) and
(ep2.entrada_produto.lote = ep1.lote))) as saldo,
ep1.id_unidade_produto,
unidades_produtos.unidade,
ep1.lote,
ep1.envase,
ep1.data_validade,
ep1.data_entrada,
ep1.id_empresa,
empresas.nome_fantasia,
ep1.observacao
from
entrada_produto as ep1
left join grupos_produtos on
ep1.id_grupo_produto = grupos_produtos.id_grupo
left join sub_grupos_produtos on
ep1.id_sub_grupo_produto = sub_grupos_produtos.id_sub_grupo
left join produtos on
ep1.codigo_produto = produtos.codigo
left join unidades_produtos on
ep1.id_unidade_produto = unidades_produtos.id
left join empresas on
ep1.id_empresa = empresas.id_empresa
Estou utilizando o Firebird 2.0
Se alguém puder dar uma força, agradeço.
Abraços.
Editado pela última vez por Aggelos em Qui Nov 23, 2017 1:28 pm, num total de 1 vez |
|
| Voltar ao Topo |
|
 |
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
|
| Voltar ao Topo |
|
|
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
| Enviada: Qua Nov 22, 2017 9:27 am Assunto: |
|
|
| imex escreveu: | Bom dia,
Você pode postar um exemplo com uma pequena amostra dos registros existentes na tabela e o respectivo resultado esperado?
|
Salve, imex. Vou enviar duas imagens dos dados nas tabelas e descrever mais abaixo o resultado esperado pela SQL.
Tabela entrada_produto
Tabela saida_produto
Eu preciso pegar os campos:
ep1.id_grupo_produto,
grupos_produtos.nome_grupo,
ep1.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
codigo_produto,
produtos.nome,
data_fabricacao,
*** fazer o somatório das entradas e diminuir do somatório das saídas (campo quantidade nas duas tabelas) por produto pegando o código do produto e o lote e criar a coluna: saldo,
id_unidade_produto,
unidades_produtos.unidade,
lote,
envase,
data_validade,
data_entrada,
id_empresa,
empresas.nome_fantasia
Qualquer dúvida é só postar mensagem que eu respondo.
Obrigado desde já pela ajuda. |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Nov 22, 2017 10:18 am Assunto: |
|
|
Não sei se vai funcionar mas experimente fazer uns testes com a query abaixo:
| Código: | select
ep1.id_grupo_produto,
grupos_produtos.nome_grupo,
ep1.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
ep1.codigo_produto,
produtos.nome,
ep1.data_fabricacao,
sum(ep1.quantidade) -
coalesce
((select sum(saida_produto.quantidade)
from saida_produto
where
ep1.codigo_produto = saida_produto.codigo_produto and
ep1.lote = saida_produto.lote), 0) as saldo,
ep1.id_unidade_produto,
unidades_produtos.unidade,
ep1.lote,
ep1.envase,
ep1.data_validade,
ep1.data_entrada,
ep1.id_empresa,
empresas.nome_fantasia,
ep1.observacao
from entrada_produto as ep1
left join grupos_produtos on
ep1.id_grupo_produto = grupos_produtos.id_grupo
left join sub_grupos_produtos on
ep1.id_sub_grupo_produto = sub_grupos_produtos.id_sub_grupo
left join produtos on
ep1.codigo_produto = produtos.codigo
left join unidades_produtos on
ep1.id_unidade_produto = unidades_produtos.id
left join empresas on
ep1.id_empresa = empresas.id_empresa
group by
ep1.id_grupo_produto,
grupos_produtos.nome_grupo,
ep1.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
ep1.codigo_produto,
produtos.nome,
ep1.data_fabricacao,
ep1.id_unidade_produto,
unidades_produtos.unidade,
ep1.lote,
ep1.envase,
ep1.data_validade,
ep1.data_entrada,
ep1.id_empresa,
empresas.nome_fantasia,
ep1.observacao
|
Espero que ajude |
|
| Voltar ao Topo |
|
|
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
| Enviada: Qua Nov 22, 2017 2:31 pm Assunto: |
|
|
| imex escreveu: | Não sei se vai funcionar mas experimente fazer uns testes com a query abaixo:
Espero que ajude |
Salve, imex.
Sua SQL já ajudou bastante pois baseado nela, criei a SQL abaixo que me trás alguns valores corretos porém, outros errados... :/
SQL:
| Código: | select
ep1.id_grupo_produto,
grupos_produtos.nome_grupo,
ep1.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
ep1.codigo_produto,
produtos.nome,
ep1.data_fabricacao,
((select sum(ep2.quantidade) from entrada_produto as ep2 where ((ep1.codigo_produto = ep2.codigo_produto) and (ep1.lote = ep2.lote) and (ep1.id_empresa = ep2.id_empresa))) -
(select sum(saida_produto.quantidade) from saida_produto where ((ep1.codigo_produto = saida_produto.codigo_produto) and (ep1.lote = saida_produto.lote) and (ep1.id_empresa = saida_produto.id_empresa)))) as saldo,
ep1.id_unidade_produto,
unidades_produtos.unidade,
ep1.lote,
ep1.envase,
ep1.data_validade,
ep1.data_entrada,
ep1.id_empresa,
empresas.nome_fantasia
from entrada_produto as ep1
left join grupos_produtos on
ep1.id_grupo_produto = grupos_produtos.id_grupo
left join sub_grupos_produtos on
ep1.id_sub_grupo_produto = sub_grupos_produtos.id_sub_grupo
left join produtos on
ep1.codigo_produto = produtos.codigo
left join unidades_produtos on
ep1.id_unidade_produto = unidades_produtos.id
left join empresas on
ep1.id_empresa = empresas.id_empresa |
A SQL acima me retorna a seguinte listagem como na imagem abaixo:
Mas o saldo real, isto é, a soma de todas as entradas menos a soma de todas as saídas é a imagem abaixo:
O resultado da SQL teria que trazer uma listagem como na imagem acima...
Se você puder me dar uma luz de onde estou errando, agradeço imensamente.
Eu removi a função coalesce pois sempre haverá algum valor em "saldo".
Removi o group by para ver se funcionava e funcionou sem ele, então deixei sem para a SQL ficar menor um pouco.
Abraço. |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Nov 22, 2017 6:52 pm Assunto: |
|
|
Você pode postar como ficou o resultado da query que sugeri? O que deu errado ou faltou?
Você retirou o Group By mas em compensação adicionou o trecho abaixo, onde é feita nova leitura dos registros da tabela entrada_produto, tornando a query mais "pesada". Não sei se é necessário deixar dessa forma.
| Código: | ((select sum(ep2.quantidade) from entrada_produto as ep2 where ((ep1.codigo_produto = ep2.codigo_produto) and (ep1.lote = ep2.lote) and (ep1.id_empresa = ep2.id_empresa))) -
|
Sobre o Coalesce, utilizei o mesmo para evitar que o saldo fique null como está na imagem que você postou quando o item não tiver saída. |
|
| Voltar ao Topo |
|
|
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
| Enviada: Qui Nov 23, 2017 8:49 am Assunto: |
|
|
| imex escreveu: | Você pode postar como ficou o resultado da query que sugeri? O que deu errado ou faltou?
Você retirou o Group By mas em compensação adicionou o trecho abaixo, onde é feita nova leitura dos registros da tabela entrada_produto, tornando a query mais "pesada". Não sei se é necessário deixar dessa forma.
| Código: | ((select sum(ep2.quantidade) from entrada_produto as ep2 where ((ep1.codigo_produto = ep2.codigo_produto) and (ep1.lote = ep2.lote) and (ep1.id_empresa = ep2.id_empresa))) -
|
Sobre o Coalesce, utilizei o mesmo para evitar que o saldo fique null como está na imagem que você postou quando o item não tiver saída. |
Bom dia, imex.
Abaixo vai a imagem com o resultado da sua query inicial:
Veja que ela trás um resultado zerado e um negativo, mas como postei na imagem abaixo, o "saldo" está positivo para todos os itens.
Eu criei a rotina do sub-select pois pensei ser necessário para fazer o somatório das quantidades dos produtos respeitando a empresa e o lote. Da forma que você fez, pensei que somaria tudo, sem haver a separação por empresa e lote.
Para falar a verdade já estou pensando em mudar essa estrutura, só não consegui pensar em uma outra que eu consiga esse saldo de maneira mais fácil e utilizando esses campos pois eles não podem sair...
Mais uma vez obrigado pela grande ajuda. |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qui Nov 23, 2017 9:21 am Assunto: |
|
|
Bom dia,
Como você obteve esse resultado da segunda imagem?
O saldo apresentado nesse resultado está separado por empresa, produto e lote?
Não sei se é coincidência mas repare que o saldo do produto 2 da empresa 1 é 1400, e na query que sugeri aparecem 2 linhas para esse produto, uma de 800 e outra de 600, cuja soma é 1400. |
|
| Voltar ao Topo |
|
|
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
| Enviada: Qui Nov 23, 2017 9:43 am Assunto: |
|
|
| imex escreveu: | Bom dia,
Como você obteve esse resultado da segunda imagem?
O saldo apresentado nesse resultado está separado por empresa, produto e lote?
Não sei se é coincidência mas repare que o saldo do produto 2 da empresa 1 é 1400, e na query que sugeri aparecem 2 linhas para esse produto, uma de 800 e outra de 600, cuja soma é 1400. |
Eu obtive esse resultado na segunda imagem somando as entradas dos produtos e diminuindo da soma das saídas, mas sem filtro algum (de lote e empresa). Assim fica fácil pois o que está dando problema é na hora de incluir o lote e empresa na query. Porém, como os produtos são feitos por lotes, eu preciso saber qual produto está sendo vendido/transferido e quais estão disponíveis no estoque filtrando pelo lote... :/
O 800 + 600 é coincidência pois nessas imagens abaixo (que estão lá no topo do post) eu mostro todas as entradas e saídas que existem (que fiz para teste) e não tem nenhuma entrada ou saída de 600.
O que percebi é que para os produtos com somente uma entrada e uma saída, a query funciona, mas para os produtos com mais de uma entrada e mais de uma saída, gera resultado errado.
Tabela com as entradas:
Tabela com as saídas:
|
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qui Nov 23, 2017 10:14 am Assunto: |
|
|
| Experimente fazer uns testes removendo o campo ep1.data_entrada da query (após o Select e o Group By). |
|
| Voltar ao Topo |
|
|
Aggelos
Novato
Registrado: Sexta-Feira, 18 de Abril de 2008
Mensagens: 69
|
| Enviada: Qui Nov 23, 2017 1:39 pm Assunto: |
|
|
| imex escreveu: | | Experimente fazer uns testes removendo o campo ep1.data_entrada da query (após o Select e o Group By). |
Boa tarde, imex.
Consegui resolver.
Como dizia um professor de faculdade: "O bom é inimigo do ótimo. Se você não consegue fazer o ótimo, faço o bom."
Ao invés de gerar um estoque para as entradas e saídas, juntos, eu criei uma visão que será o estoque para as entradas e uma visão que será o estoque para as saídas. As duas visões têm os mesmos campos, assim eu pude fazer uma SQL para selecionar as entradas mas subtraindo o "saldo" das saídas. Abaixo vão os códigos criados, de repente pode ajudar mais algum membro aqui do Fórum. 
Visão criada para o estoque das entradas:
| Código: | create view vw_estoque_entrada_produto (
id_grupo_produto,
nome_grupo,
id_sub_grupo_produto,
nome_sub_grupo,
codigo_produto,
nome,
id_unidade_produto,
unidade,
data_fabricacao,
lote,
envase,
data_validade,
data_entrada,
saldo,
id_empresa,
nome_fantasia)
as
select
entrada_produto.id_grupo_produto,
grupos_produtos.nome_grupo,
entrada_produto.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
entrada_produto.codigo_produto,
produtos.nome,
entrada_produto.id_unidade_produto,
unidades_produtos.unidade,
entrada_produto.data_fabricacao,
entrada_produto.lote,
entrada_produto.envase,
entrada_produto.data_validade,
entrada_produto.data_entrada,
sum (entrada_produto.quantidade) as saldo,
entrada_produto.id_empresa,
empresas.nome_fantasia
from
entrada_produto
left join grupos_produtos on
entrada_produto.id_grupo_produto = grupos_produtos.id_grupo
left join sub_grupos_produtos on
entrada_produto.id_sub_grupo_produto = sub_grupos_produtos.id_sub_grupo
left join produtos on
entrada_produto.codigo_produto = produtos.codigo
left join unidades_produtos on
entrada_produto.id_unidade_produto = unidades_produtos.id
left join empresas on
entrada_produto.id_empresa = empresas.id_empresa
group by
entrada_produto.id_grupo_produto,
grupos_produtos.nome_grupo,
entrada_produto.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
entrada_produto.codigo_produto,
produtos.nome,
entrada_produto.data_fabricacao,
entrada_produto.id_unidade_produto,
unidades_produtos.unidade,
entrada_produto.lote,
entrada_produto.envase,
entrada_produto.data_validade,
entrada_produto.data_entrada,
entrada_produto.id_empresa,
empresas.nome_fantasia |
Visão criada para o estoque das saídas:
| Código: | create view vw_estoque_saida_produto (
ID_GRUPO_PRODUTO,
NOME_GRUPO,
ID_SUB_GRUPO_PRODUTO,
NOME_SUB_GRUPO,
CODIGO_PRODUTO,
NOME,
ID_UNIDADE_PRODUTO,
UNIDADE,
DATA_FABRICACAO,
LOTE,
ENVASE,
DATA_VALIDADE,
DATA_saida,
SALDO,
ID_EMPRESA,
NOME_FANTASIA)
as
select
saida_produto.id_grupo_produto,
grupos_produtos.nome_grupo,
saida_produto.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
saida_produto.codigo_produto,
produtos.nome,
saida_produto.id_unidade_produto,
unidades_produtos.unidade,
saida_produto.data_fabricacao,
saida_produto.lote,
saida_produto.envase,
saida_produto.data_validade,
saida_produto.data_saida,
sum (saida_produto.quantidade) as saldo,
saida_produto.id_empresa,
empresas.nome_fantasia
from
saida_produto
left join grupos_produtos on
saida_produto.id_grupo_produto = grupos_produtos.id_grupo
left join sub_grupos_produtos on
saida_produto.id_sub_grupo_produto = sub_grupos_produtos.id_sub_grupo
left join produtos on
saida_produto.codigo_produto = produtos.codigo
left join unidades_produtos on
saida_produto.id_unidade_produto = unidades_produtos.id
left join empresas on
saida_produto.id_empresa = empresas.id_empresa
group by
saida_produto.id_grupo_produto,
grupos_produtos.nome_grupo,
saida_produto.id_sub_grupo_produto,
sub_grupos_produtos.nome_sub_grupo,
saida_produto.codigo_produto,
produtos.nome,
saida_produto.data_fabricacao,
saida_produto.id_unidade_produto,
unidades_produtos.unidade,
saida_produto.lote,
saida_produto.envase,
saida_produto.data_validade,
saida_produto.data_saida,
saida_produto.id_empresa,
empresas.nome_fantasia |
SQL criada para gerar o estoque:
| Código: | select
vw_estoque_entrada_produto.id_grupo_produto,
vw_estoque_entrada_produto.nome_grupo,
vw_estoque_entrada_produto.id_sub_grupo_produto,
vw_estoque_entrada_produto.nome_sub_grupo,
vw_estoque_entrada_produto.codigo_produto,
vw_estoque_entrada_produto.nome,
vw_estoque_entrada_produto.id_unidade_produto,
vw_estoque_entrada_produto.unidade,
vw_estoque_entrada_produto.data_fabricacao,
vw_estoque_entrada_produto.lote,
vw_estoque_entrada_produto.envase,
vw_estoque_entrada_produto.data_validade,
vw_estoque_entrada_produto.data_entrada,
coalesce((vw_estoque_entrada_produto.saldo - vw_estoque_saida_produto.saldo), vw_estoque_entrada_produto.saldo) as saldo,
vw_estoque_entrada_produto.id_empresa,
vw_estoque_entrada_produto.nome_fantasia
from
vw_estoque_entrada_produto
left join vw_estoque_saida_produto on
vw_estoque_entrada_produto.codigo_produto = vw_estoque_saida_produto.codigo_produto and
vw_estoque_entrada_produto.lote = vw_estoque_saida_produto.lote and
vw_estoque_entrada_produto.id_empresa = vw_estoque_saida_produto.id_empresa |
Agora é alterar as rotinas no Delphi... 
Obrigado mais uma vez pela ajuda, amigo. Se passar por Friburgo/RJ, avise para trocarmos uma ideia.
Abraços. |
|
| Voltar ao Topo |
|
|
|
|
Enviar Mensagens Novas: Proibido.
Responder Tópicos Proibido
Editar Mensagens: Proibido.
Excluir Mensagens: Proibido.
Votar em Enquetes: Proibido.
|
|
FAQ
Pesquisar
Membros
Grupos
Registrar
Perfil
Entrar e ver Mensagens Particulares
Entrar
