Duvida SQL, me ajudemmmm.
11/11/2014
0
Tenho uma tabela de controle de estoque:
cd_movimentacao int not null, cd_material int not null, cd_pessoa int not null, qt_movimentacao decimal, tp_movimentacao varchar(2), dt_movimentacao datetime,
Numa consulta SQL preciso que seja retornado nm_material, qt_entrada, qt_saida e qt_saldo, alguém pode me ajudar?
No aguardo.
Totvs Sa
Post mais votado
20/11/2014
Eu já vi que entre o Oracle, PostgreSQL e SQLServer algumas funções e algumas particularidades da sintaxe são diferentes.
Abraço.
Marisiana Battistella
Mais Posts
11/11/2014
Soeuseijothaz
Coloque os insert com alguns dados simulado sua base e post de volta, isso vai facilitar a ajuda.
declare @controle table( cd_movimentacao int not null, cd_material int not null, cd_pessoa int not null, qt_movimentacao decimal, tp_movimentacao varchar(2), dt_movimentacao datetime ) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (1,100,0,5,'e',GETDATE()) select * from @controle
11/11/2014
Wellington Carvalho
SELECT b.nm_material, b.qt_entrada, b.qt_saida, b.qt_saldo FROM ControleEstoque a INNER JOIN Material b ON a.cd_material = b.cd_material WHERE (Condicao de pesquisa na tabela ControleEstoque que retorna os campos da tabela Material)
11/11/2014
Marisiana Battistella
Você tem que ver em quais tabelas estão as informação " nm_material, qt_entrada, qt_saida e qt_saldo" que você precisa que o select retorne, ver quais são as Foreign Key existentes entre elas e, em seguida, construir o select referenciando as tabelas corretamente.
11/11/2014
Soeuseijothaz
No meu entendimento acho que o questionamento do post é um pouco mais complexo.
Os campos qt_entrada, qt_saida e qt_saldo deve ser calculados.
Então qt_entrada e qt_saida será a soma qt_movimentacao dependendo do tp_movimentacao.
E qt_saldo será qt_entrada - qt_saida.
Por isso postei o script para ter uma ideia do conteúdo do dados. Como gostei do desafio fiz uma base de teste.
Então fiz duas provas de conceito.
Exemplo 1 - subqueies
declare @controle table( cd_movimentacao int not null, cd_material int not null, cd_pessoa int not null, qt_movimentacao decimal, tp_movimentacao varchar(2), dt_movimentacao datetime ) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (1,100,0,50,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (2,100,0,80,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,50,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (4,200,0,10,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (5,100,0,100,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,200,0,30,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,100,0,10,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,30,'e',GETDATE()) select c.cd_material , 'Entrada' = ( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 'e' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao ) , 'Saida' = ( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 's' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao ) , saldo = (( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 'e' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao ) - ( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 's' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao )) from @controle c group by c.cd_material
Exemplo 2 - subqueries e tabela temporária
declare @controle table( cd_movimentacao int not null, cd_material int not null, cd_pessoa int not null, qt_movimentacao decimal, tp_movimentacao varchar(2), dt_movimentacao datetime ) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (1,100,0,50,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (2,100,0,80,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,50,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (4,200,0,10,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (5,100,0,100,'e',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,200,0,30,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,100,0,10,'s',GETDATE()) insert into @controle (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,30,'e',GETDATE()) declare @resultado table ( cd_material int, qtde_entrada int, qtde_saida int, qtde_saldo int) insert into @resultado (cd_material,qtde_entrada,qtde_saida) (select c.cd_material , 'Entrada' = ( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 'e' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao ) , 'Saida' = ( select sum(cc.qt_movimentacao) from @controle cc where cc.tp_movimentacao = 's' and c.cd_material = cc.cd_material group by cc.cd_material,cc.tp_movimentacao ) from @controle c group by c.cd_material) update @resultado set qtde_saldo = qtde_entrada - qtde_saida select * from @resultado
Resulado será:
[img]http://arquivo.devmedia.com.br/forum/imagem/238223-20141111-192607.png[/img]
No exemplo 1 um é tudo feito em uma única expressão e pode afetar a performance.
No exemplo 2 é feito por parte e pode ser mais leve.
Então cabe a quem for usar decidir qual utilizar.
Em qualquer um dos exemplos basta acrescentar o JOIN com a tabela Material e buscar pela Descrição do Material.
Espero que eu tenha entendido e não esteja viajando, por que sou meio lerdo. kkkkkkkkkkkk
12/11/2014
Wellington Carvalho
12/11/2014
Marisiana Battistella
Ele pode apenas fazer o seguinte SELECT:
SELECT result.nmmaterial, result.qt_entrada, result.qt_saida, (result.qt_entrada - result.qt_saida) as qt_saldo FROM ( SELECT mat.nm_material, CASE WHEN tp_movimentacao = 'entrada' THEN SUM(mov.qt_movimentacao) END qt_entrada, CASE WHEN tp_movimentacao = 'saida' THEN SUM(mov.qt_movimentacao) END qt_saida FROM movimentacao mov INNER JOIN material mat ON mov.cd_material = mat.cd_material GROUP by nm_material, tp_movimentacao ) result ORDER BY result.nm_material;
Porém ele precisa analisar qual é a regra que deve ser atendida para gerar o qt_saldo, pois, se for o saldo atual do estoque, pode ser que exista algum campo que armazene essa informação.
12/11/2014
Soeuseijothaz
Marisiana, realmente sua abordagem foi bem mais elegante e robusta que a minha.
E esta é a beleza da programação a multiplicidade de visão.
Como estou interessado em entender qual a melhor solução para um cenário deste, criei uma base de dados temporária e rodei o script proposto, o resultado não calculou a qt_saldo, além de duplicar as linha, veja:
[img]http://arquivo.devmedia.com.br/forum/imagem/238223-20141112-121013.png[/img]
O script com as tabelas temporárias para teste:
declare @movimentacao table( cd_movimentacao int not null, cd_material int not null, cd_pessoa int not null, qt_movimentacao decimal, tp_movimentacao varchar(2), dt_movimentacao datetime ) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (1,100,0,50,'e',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (2,100,0,80,'s',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,50,'e',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (4,200,0,10,'s',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (5,100,0,100,'e',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,200,0,30,'s',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (6,100,0,10,'s',GETDATE()) insert into @movimentacao (cd_movimentacao, cd_material,cd_pessoa,qt_movimentacao,tp_movimentacao,dt_movimentacao) values (3,200,0,30,'e',GETDATE()) declare @material table( cd_material int not null, nm_material varchar(20) ) insert into @material (cd_material,nm_material) values (100,'Material 100') insert into @material (cd_material,nm_material) values (200,'Material 200') SELECT result.nm_material, result.qt_entrada, result.qt_saida, (result.qt_entrada - result.qt_saida) as qt_saldo FROM ( SELECT mat.nm_material, tp_movimentacao, CASE WHEN tp_movimentacao = 'e' THEN SUM(mov.qt_movimentacao) END qt_entrada, CASE WHEN tp_movimentacao = 's' THEN SUM(mov.qt_movimentacao) END qt_saida FROM @movimentacao mov INNER JOIN @material mat ON mov.cd_material = mat.cd_material GROUP by nm_material, tp_movimentacao ) result ORDER BY result.nm_material
Como contornar isto?
12/11/2014
Marisiana Battistella
Expuz uma idéia para ser trabalhada e adaptada conforme a estrutura do banco de dados ao qual a instrução será aplicada.
Eu já utilizei uma estrutura assim para resolver vários problemas semelhantes e funcionou.
No teu teste com as tabelas temporárias não há a criação das Foreign Key com as demais tabelas envolvidas.
12/11/2014
Marisiana Battistella
SELECT result.nmmaterial, result.qt_entrada, result.qt_saida, (result.qt_entrada - result.qt_saida) as qt_saldo FROM ( SELECT mat.nm_material, CASE WHEN tp_movimentacao = 'entrada' THEN SUM(mov.qt_movimentacao) END qt_entrada, CASE WHEN tp_movimentacao = 'saida' THEN SUM(mov.qt_movimentacao) END qt_saida FROM movimentacao mov INNER JOIN material mat ON mov.cd_material = mat.cd_material WHERE tp_movimentacao in ('entrada', 'saida') GROUP by nm_material, tp_movimentacao ) result ORDER BY result.nm_material;
12/11/2014
Soeuseijothaz
SELECT result.nmmaterial, result.qt_entrada, result.qt_saida, (result.qt_entrada - result.qt_saida) as qt_saldo FROM ( SELECT mat.nm_material, CASE WHEN tp_movimentacao = 'entrada' THEN SUM(mov.qt_movimentacao) END qt_entrada, CASE WHEN tp_movimentacao = 'saida' THEN SUM(mov.qt_movimentacao) END qt_saida FROM movimentacao mov INNER JOIN material mat ON mov.cd_material = mat.cd_material WHERE tp_movimentacao in ('entrada', 'saida') GROUP by nm_material, tp_movimentacao ) result ORDER BY result.nm_material;
Marisiana,
Antes de mais nada quero deixar claro que em nenhum momento tive a intenção de criticar de forma destrutiva a sua contribuição. Pelo contrário achei ótima e válida.
Se não fiz entender corretamente peço desculpas.
Sei que a maioria das pessoas não tem tempo para ficar criando masa de dados para post de fóruns, pois tem outras ocupações. Infelizmente estou me recuperando de um procedimento cirúrgico e o que mais tenho é tempo, pois tenho de ficar de repouso absoluto. Então para não enferrujar estou estudando.
Quando fiz o questionamento é porque realmente queria me aprofundar e entender. E acho que este post ajudará muita gente.
Criei as tabelas com PK e FK rodei o script que você postou e o retorno continua como na imagem que postei anteriormente duplicando os produtos e sem quantificar o saldo.
Devo estar fazendo alguma coisa errada, vou quebrar a cabeça aqui e ver onde chego.
Agradeço sua presteza e disposição para compartilhar conhecimento.
12/11/2014
Marisiana Battistella
Eu executei esse último SQL em uma base de dados que tenho aqui e funcionou perfeitamente.
O uso do CASE é mais viável para esse caso, ele é um recurso disponível praticamente em todos os bancos.
Utilizar subselect, além de não ser considerada uma boa prática, compromete a performance.
No exemplo que você postou, você utilizou 4 consultas (subselect) para obter um resultado que pode ser obtido em uma única consulta, pois a estrutura da tabela permite isso.
12/11/2014
Soeuseijothaz
O exemplo que postei seria somente uma forma de dar um norte, quantidade de subselect pode complicar e cheguei a frisar isto no post.
Então você esta coberta de razão ao sugerir o uso do case.
Eu devo esta fazendo algo errado pois crie as tabelas fisicamente com todos os atributos e não retorna o esperado.
Como você testou e funcionou devo esta fazendo caca. kkkkk
Normalmente quando acontecem estes problemas dizem que erro de IO, ou seja Ignorância do Operador. kkk
Só como informação existe um erro no script:
SELECT result.nmmaterial,
Deve ser:
SELECT result.nm_material,
Mais uma vez obrigado pelo tempo dispensado e sua assertividade
12/11/2014
Alex Lekao
deixar uma pequena participacao correndo.. .rsrsr
sugiro no exemplo dado pela Marisiana onde os resultados duplicaram as linhas, usar o isnull deixando o valor que seria nulo como zero e usar o group by com o sum das colunas, assim ficara tudo em uma unica linha e conseguira somar pq nao havera campos nulos.
Pecos desculpas por entrar assim no post, pegando carona e tentando sentar na janela, rsrsr, mas acredito que atendera.
como estou de saida do trabalho nao tive tempo de fazer testes e analisar a estrutura.
Abraco.
P.S.: se fiz alguma citacao errada, me desculpe. rsrsr
13/11/2014
Marisiana Battistella
Os resultados estavam duplicando, pois eu não tinha inserido a clausula WHERE no primeiro exemplo que postei e, com isso, não estava restringindo o retorno.
Com essa estrutura não duplica:
SELECT result.nm_material, result.qt_entrada, result.qt_saida, (result.qt_entrada - result.qt_saida) as qt_saldo FROM ( SELECT mat.nm_material, CASE WHEN tp_movimentacao = 'entrada' THEN SUM(mov.qt_movimentacao) END qt_entrada, CASE WHEN tp_movimentacao = 'saida' THEN SUM(mov.qt_movimentacao) END qt_saida FROM movimentacao mov INNER JOIN material mat ON mov.cd_material = mat.cd_material WHERE tp_movimentacao in ('entrada', 'saida') GROUP by nm_material, tp_movimentacao ) result ORDER BY result.nm_material;
Eu só fiz um teste semelhante em uma das bases teste que aqui e adaptei a estrutura conforme as informações apresentadas no inicio deste tópico.
Obrigada pela participação!!
13/11/2014
Alex Lekao
Sem problema.
Como eu vi no final do dia, estava saindo e havia passado por problema parecido recentemente resolvi sugerir algo.
Um problema muito comum que as vezes enfrento eh quando utilizado operacoes de calculos e tal onde tem ums dos resultados que sera somando com null, ele nao soma corretamente, por isso sugeri o isnull para que tratar o resultado com zero e soma ficar correta, pelo menos entendi que fosse isso que estavam querendo fazer. rsrsr
Mas ele esta sendo muito bem acessorado por vcs.
Abraco.
Clique aqui para fazer login e interagir na Comunidade :)