Duvida SQL, me ajudemmmm.

SQL Server

11/11/2014

Boa tarde,

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

Totvs Sa

Curtidas 0

Melhor post

Marisiana Battistella

Marisiana Battistella

20/11/2014

Sim Alex, já trabalhei com Oracle e agora trabalho com PostgreSQL.
Eu já vi que entre o Oracle, PostgreSQL e SQLServer algumas funções e algumas particularidades da sintaxe são diferentes.
Abraço.
GOSTEI 1

Mais Respostas

Soeuseijothaz

Soeuseijothaz

11/11/2014

Olha fiz um script para criar um base de dados temporária.

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
GOSTEI 0
Wellington Carvalho

Wellington Carvalho

11/11/2014

Boa tarde, criei uma estrutura onde você tem a tabela de ControleEstoque e Material utilizando inner join ta pra trazer resultado da tabela Material.
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)
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

O exemplo que o Wellington postou se aproxima do que você precisa fazer.
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.
GOSTEI 0
Soeuseijothaz

Soeuseijothaz

11/11/2014

Wellington Cicero de Carvalho e Marisiana,

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
GOSTEI 0
Wellington Carvalho

Wellington Carvalho

11/11/2014

Bom dia utilizando o código que passeI inclua no mesmo o COUNT e GROUP BY para ter os valores. Se possível post a estrutura das duas tabelas que ajudamos você
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Entendi o que você propôs Jothaz, mas não precisa de tudo isso...
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.
GOSTEI 0
Soeuseijothaz

Soeuseijothaz

11/11/2014

Bom vamos esperar o dono do post se manifestar. kkkk

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?
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Eu não fiquei fazendo testes, pois meu objetivo aqui não é passar uma resposta pronta.
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.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Agora fiz um teste e só faltou incluir a clausula WHERE no 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
        WHERE tp_movimentacao in ('entrada', 'saida')
	GROUP by nm_material,
		 tp_movimentacao ) result
ORDER BY result.nm_material;
GOSTEI 0
Soeuseijothaz

Soeuseijothaz

11/11/2014

Agora fiz um teste e só faltou incluir a clausula WHERE no 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
        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.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Não fiquei incomodada... desculpa se a forma como me expressei deu a entender isso.

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.
GOSTEI 0
Soeuseijothaz

Soeuseijothaz

11/11/2014

Estou enferrujado no uso do SQL ,na verdade nunca fui expert só curioso mesmo. Ultimamente tenho trabalho com Linq e Lambda Expressions e assim acabamos nos esquecendo do SQL. kkkk

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
GOSTEI 0
Alex Lekao

Alex Lekao

11/11/2014

Ola, boa noite!!!

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
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Olá Alex!
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!!
GOSTEI 0
Alex Lekao

Alex Lekao

11/11/2014

Oi Marisiana, bom dia!!!

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.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

11/11/2014

Interessante isso que vc relatou Alex!
Não cheguei a ter uma situação assim para resolver, mas vou lembrar da tua dica se surgir alguma situação assim. =)
GOSTEI 0
Alex Lekao

Alex Lekao

11/11/2014

Blz...

que bom que foi util. rsrsr

Mas isso foi uma situacao que percebi no SQL Server, como sei que vc lida com Oracle e Postgree(certo? rsrs) nao sei se o caso eh o mesmo.

Abraco.
GOSTEI 0
POSTAR