Somando Campo - Trigger
21/10/2013
0
é a primeria vez que posto aqui, espero poder aprender bastante com voces, e ensinar um pouco do que sei =)
Bom ... vamos la ...
Estou com um probleminha.
Tenho uma tabela SAIDA_PRODUTO, onde faço a inserção dos produtos solicitados, nela contem os seguintes campos: data da solicitação, nome do setor solicitante, o tipo do produto e a quantidade.
Então, ai é que entra problema.
Toda vez que é solicitado um produdo, ocorre uma duplicidade de setor.
Ai criei uma trigger, que me informa todos os dados acima.
Isso vai para um relatório mensal, o relatório é constituído por uma view que contem inner join com 3 tabelas, fiz algumas alterações na view, depois da criação da trigger, estaria tudo perfeito, se não fosse o caso da trigger me retornar várias vezes, igual estava anteriormente.
Eu queria apenas que pegasse a setor solicitante e somasse a quantidade do produto solicitado por ele.
Simone
Posts
22/10/2013
Simone
Ele me retorna 1 pra cada setor, sendo que tem setor zerado, e se eu acrescento mais 1 em um setor que ja tem, totalizando 2, ele retorna 2 pra todos.
SELECT DISTINCT
CASE
WHEN MONTH(DATA_SAIDA) = 01 THEN 'Janeiro'
WHEN MONTH(DATA_SAIDA) = 02 THEN 'Fevereiro'
WHEN MONTH(DATA_SAIDA) = 03 THEN 'Março'
WHEN MONTH(DATA_SAIDA) = 04 THEN 'Abril'
WHEN MONTH(DATA_SAIDA) = 05 THEN 'Maio'
WHEN MONTH(DATA_SAIDA) = 06 THEN 'Junho'
WHEN MONTH(DATA_SAIDA) = 07 THEN 'Julho'
WHEN MONTH(DATA_SAIDA) = 08 THEN 'Agosto'
WHEN MONTH(DATA_SAIDA) = 09 THEN 'Setembro'
WHEN MONTH(DATA_SAIDA) = 10 THEN 'Outubro'
WHEN MONTH(DATA_SAIDA) = 11 THEN 'Novembro'
WHEN MONTH(DATA_SAIDA) = 12 THEN 'Dezembro'
end Mes,
DATEPART(YEAR,GETDATE()) Ano ,
dir.NOME_DIRE Diretoria ,
count (distinct ap.QUANTIDADE) Quantidade
FROM tb_Atualização_Produto ap
INNER JOIN tb_SAIDA sa
ON ap.ID_PRODUTO = sa.ID_PRODUTO
INNER JOIN FUNCIONARIOS.dbo.tb_DIRETORIA dir
ON sa.ID_DIRE = dir.ID_DIRE
GROUP BY ap.DATA_SAIDA,dir.NOME_DIRE, ap.QUANTIDADE
23/10/2013
Alex Lekao
Para a data eu sugeriria vc usar assim substring(convert(varchar,DATA_SAIDA,106),4,9) nao apresenta todo o nome do mes, mas acredito que fique legal, pq tem parte do nome do mes e sem o uso do case deve ficar mais rapido o script.
Dependendo do que esta querendo, utilize uma sub select para ver se resolve.
Eu particularmente nao entendi muito bem o que esta querendo.
Espero ter ajudado.
Abraco.
Alex - Lekao
24/10/2013
Simone
Eu ja fiz um subselect
with TONNER as
(
SELECT
case
when isnull (c.Setor, ' ') = ' ' then p.Setor
when isnull (p.Setor, ' ') = ' ' then c.Setor
else ' '
end Setor,
p.Preto,
c.Colorido
FROM (
SELECT
--substring(NOME_PRODUTO, 15, 1),
sa.DATA_SAIDA,
dir.NOME_SETOR Setor,
SUM(QUANTIDADE) Colorido
FROM [CONTROLE].[dbo].[tb_PRODUTO] pr
inner join tb_SAIDA sa
ON pr.ID_PRODUTO = sa.ID_PRODUTO
inner join FUNCIONARIOS.dbo.tb_Setor dir
ON dir.ID_DIRE = sa.ID_DIRE
WHERE substring(pr.NOME_PRODUTO, 15, 1)in('y', 'C', 'M')
-- AND MONTH(DATA_SAIDA) = DATEPART(mm,Getdate()) -1
GROUP BY dir.NOME_SETOR,sa.DATA_SAIDA
) C
full join
(
SELECT
sa.DATA_SAIDA,
dir.NOME_SETOR Setor,
SUM(QUANTIDADE) Preto
FROM [CONTROLE].[dbo].[tb_PRODUTO] pr
inner join tb_SAIDA sa
ON pr.ID_PRODUTO = sa.ID_PRODUTO
inner join FUNCIONARIOS.dbo.tb_Setor dir
ON dir.ID_DIRE = sa.ID_DIRE
WHERE substring(NOME_PRODUTO, 15, 1) = 'K' or substring(NOME_PRODUTO, 16,1) = 'L'
GROUP BY DIR.NOME_SETOR,sa.DATA_SAIDA
) P
ON C.Setor = P.Setor
)
SELECT
Setor ,
isnull (preto, 0) Preto,
isnull (colorido,0) Colorido,
isnull (preto, 0) + isnull (colorido,0) Total
FROM TONNER
..
O problema é o seguinte,
Toda vez que é solicitado um produto, no caso de cima usei o tonner como exemplo. Toda vez que um setor solicitar tonner, eu insiro na tabela as seguintes informações: data, setor solicitante , a qtde solicitada e se é tonner preto ou colorido. (eu tenho uma tabela com os id's dos tonner's)
Eu criei uma view que ja me traz o que eu preciso ...
Aparece assim
setor - Preto - Colorido - Total
A- 1 - 0 - 1
B - 0 - 1 - 1
A - 0 - 1 - 1
Preciso que apareça assim
setor - Preto - Colorido - Total
A- 1 - 1 - 2
B - 0 - 1 - 1
24/10/2013
Alex Lekao
Entao esses dados que vc precisa eh referente a este script certo?
SELECT Setor , isnull (preto, 0) Preto, isnull (colorido,0) Colorido, isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER
eu sugiro um teste da seguinte forma:
SELECT Setor , sum(isnull (preto, 0)) Preto, sum(isnull (colorido,0)) Colorido, isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER group by setor
Faz um teste e posta ai se resolveu.
a alteracao seria somente neste script, que eh onde acredito que esteja apresentando os resultados.
Abraco.
Alex - Lekao
24/10/2013
Simone
Entao esses dados que vc precisa eh referente a este script certo?
SELECT Setor , isnull (preto, 0) Preto, isnull (colorido,0) Colorido, isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER
eu sugiro um teste da seguinte forma:
SELECT Setor , sum(isnull (preto, 0)) Preto, sum(isnull (colorido,0)) Colorido, isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER group by setor
Faz um teste e posta ai se resolveu.
a alteracao seria somente neste script, que eh onde acredito que esteja apresentando os resultados.
Abraco.
Alex - Lekao
Bom dia Alex
Se eu fizer o grouo by só pelo setor da esse erro
Mensagem 8120, Nível 16, Estado 1, Linha 47
Column 'TONNER.Preto' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Se faço assim, não muda nada, continua aparecendo mais de uma vez
SELECT
Setor,
isnull (preto, 0) Preto,
isnull (colorido,0) Colorido,
isnull (preto, 0) + isnull (colorido,0) Total
FROM TONNER
GROUP BY Setor,Preto, Colorido
Já estou ficando desesperada rs
24/10/2013
Alex Lekao
Acho que vc esqueceu de colocar o sum nas colunas de preto e colorido... e talvez nao seja necessario na coluta do total, caso seja acrescente tambem e veja se nao altera os dados.
fazendo isso acho que ja resolve.
24/10/2013
Simone
SELECT
Setor,
max (preto) Preto,
max (colorido) Colorido,
sum (preto) + sum (colorido) Total
FROM TONNER
GROUP BY Setor, Preto, Colorido
--
SELECT
Setor,
max (preto) Preto,
max (colorido) Colorido,
max (preto) + max (colorido) Total
FROM TONNER
GROUP BY Setor,Preto, Colorido
Esta me retornando NULL nas colunas Colorido e Total, e zero na coluna Preto, e o setor continua aparecendo mais de uma vez.
24/10/2013
Alex Lekao
seria isso:
SELECT Setor , sum(isnull (preto, 0)) Preto, -- aqui vai a soma, e a opcao de substituicao do null por zero. sum(isnull (colorido,0)) Colorido, -- aqui vai a outra soma, e a opcao de substituicao do null por zero. isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER group by setor -- aqui somente o group by do setor.
caso seja necessario, e se der erro no total, vc colocar um sum tbm no total, ficando assim:
sum(isnull(preto, 0) + isnull(colorido,0)) Total
o max eh apenas para pegar o maior valor, e se usou um sum em algum outro campo, o sql vai exigir que seja feito o agrupamento de todos os outros campos que nao tenha operacoes que precisem de agrupamento.
verifica agora se da certo.
24/10/2013
Simone
with TONNER as ( SELECT case when isnull (c.Setor, ' ') = ' ' then p.Setor when isnull (p.Setor, ' ') = ' ' then c.Setor else ' ' end Setor, p.Preto, c.Colorido FROM ( SELECT --substring(NOME_PRODUTO, 15, 1), sa.DATA_SAIDA, dir.NOME_SETOR Setor, SUM(QUANTIDADE) Colorido FROM [CONTROLE].[dbo].[tb_PRODUTO] pr inner join tb_SAIDA sa ON pr.ID_PRODUTO = sa.ID_PRODUTO inner join FUNCIONARIOS.dbo.tb_Setor dir ON dir.ID_SETOR = sa.ID_SETOR WHERE substring(pr.NOME_PRODUTO, 15, 1)in('y', 'C', 'M') GROUP BY dir.NOME_SETOR,sa.DATA_SAIDA ) C full join ( SELECT sa.DATA_SAIDA, dir.NOME_SETOR Setor, SUM(QUANTIDADE) Preto FROM [CONTROLE].[dbo].[tb_PRODUTO] pr inner join tb_SAIDA sa ON pr.ID_PRODUTO = sa.ID_PRODUTO inner join FUNCIONARIOS.dbo.tb_Setor dir ON dir.ID_SETOR = sa.ID_SETOR WHERE substring(NOME_PRODUTO, 15, 1) = 'K' or substring(NOME_PRODUTO, 16,1) = 'L' GROUP BY dir.NOME_SETOR,sa.DATA_SAIDA ) P ON C.Setor = P.Setor ) --SELECT -- Setor , -- isnull (preto, 0) Preto, -- isnull (colorido,0) Colorido, -- isnull (preto, 0) + isnull (colorido,0) Total --FROM TONNER SELECT Setor , sum(isnull (preto, 0)) Preto, -- aqui vai a soma, e a opcao de substituicao do null por zero. sum(isnull (colorido,0)) Colorido, -- aqui vai a outra soma, e a opcao de substituicao do null por zero. sum(isnull (preto, 0) + isnull (colorido,0)) Total FROM TONNER group by Setor,Preto,Colorido -- aqui somente o group by do setor. .
Alex,mto obrigada pela ajuda, ta complicadérrimo! Não mudou nada, Não vai só o group by por setor, da o erro, que mencionei acima. To te mandando a query toda,pq talvez tenha algo errado mais pra cima, e vc consiga enxerga, pq já olhei isso varias vezes, já tentei várias coisas e nada
24/10/2013
Alex Lekao
Mensagem 8120, Nível 16, Estado 1, Linha 47
Column 'TONNER.Preto' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Faz o seguinte entao, vamos ver se eh isso, os campos preto e colorido sao numericos ou caracter?
se forem numericos, remove o isnull e deixa so o sum(preto) e tal...
o group by deixa so o setor mesmo, aquela soma do total tambem remove o isnull e ve como fica.
pq nao ta fazendo sentido, essa mensagem que aparece eh normalmente porque tem algum campo que nao foi usado na instrucao group by,e como as outras tem sum e operacoes de soma e tal, nao deveriam aparecer.
vou dar mais uma olhada no codigo para ver se acho alguma coisa...
o script que vc colocou eh o script total?
25/10/2013
Simone
SELECT Diretoria , sum (preto) Preto, sum (colorido) Colorido, sum (preto) + sum (colorido) Total FROM TONNER group by Diretoria
Bom dia Alex,
Esse é o script total sim, consegui colocar o group by por diretoria.
Então quando insiro tonner preto ele faz a soma certinho, já o colorido, quando não tem nada aprece NULL, e quando insiro alguma coisa ,some o nome do setor, e vai acrescentando em apenas uma coluna
Ficou assim ..
Na primeira coluna, sumiu o nome do setor, deveria aparecer mais 2 setores, ambos com 2 colorido.
Setor - Preto - Colorido - Total
- 1 - 4 - 5
Setor1 - 0 - NULL - NULL
Setor2 - 1 - NULL - NULL
Setor3 - 0 - NULL - NULL
25/10/2013
Simone
SELECT case when isnull (c.setor, ' ') = ' ' then p.setor when isnull (p.setor, ' ') = ' ' then c.setor else ' ' end Setor, COALESCE (p.Preto, NULL,0) as Preto, COALESCE (c.Colorido, NULL,0) as Colorido
Mais continua sumindo o Setor, quando insiro tonner colorido.
25/10/2013
Alex Lekao
Me desculpe por nao esta conseguindo ajudar melhor.
Vamos fazer o seguinte?
me passa o script da tabela que esta montando o select, script de criacao da tabela e alguns inserts de conteudo para eu testar na minha base, para eu ter uma ideia melhor do que pode ser.
Eu coloco o resultado e o que conseguir montar de volta.
o que vc me diz?
25/10/2013
Simone
-- Tabela de Setor CREATE TABLE [dbo].[tb_setor]( [ID_setor] [int] IDENTITY(1,1) NOT NULL, [NOME_setor] [varchar](50) NULL ) -- Cadastro dos produtos CREATE TABLE [dbo].[tb_Estoque]( [ID_PRODUTO] [int] IDENTITY(1,1) NOT NULL, [ID_TIPO_PRODUTO] [int] NOT NULL, [NOME_PRODUTO] [varchar](20) NOT NULL, [SALDO_MINIMO] [int] NOT NULL, [SALDO] [int] NOT NULL, ) -- Inserir os produtos CREATE TABLE [dbo].[tb_TIPO_PRODUTO]( [ID_TIPO_PRODUTO] [int] IDENTITY(1,1) NOT NULL, [TIPO_PRODUTO] [varchar](50) NULL, -- Qdo é solicitado um produto, é feito atualização nessa tabela. CREATE TABLE [dbo].[tb_controle_estoque]( [ID_SAIDA] [int] IDENTITY(1,1) NOT NULL, [ID_SETOR] [int] NULL, [DATA_SAIDA] [date] NULL, [ID_PRODUTO] [int] NULL, [QUANTIDADE] [int] NULL ) -- View que seleciona os dados necessários VIEW [dbo].[v_Estoque] as with TONNER as ( SELECT case when isnull (c.setor, ' ') = ' ' then p.setor when isnull (p.setor, ' ') = ' ' then c.setor else ' ' end setor, p.Preto, c.Colorido FROM ( SELECT --substring(NOME_PRODUTO, 15, 1), sa.DATA_SAIDA, dir.NOME_SETOR setor, SUM(QUANTIDADE) Colorido FROM tb_PRODUTO pr inner join tb_SAIDA sa ON pr.ID_PRODUTO = sa.ID_PRODUTO inner join tb_setor dir ON dir.ID_SETOR = sa.SETOR WHERE substring(pr.NOME_PRODUTO, 15, 1)in('y', 'C', 'M') GROUP BY dir.NOME_SETOR,sa.DATA_SAIDA ) C full join ( SELECT sa.DATA_SAIDA, dir.NOME_SETOR setor, SUM(QUANTIDADE) Preto FROM [CONTROLE].[dbo].[tb_PRODUTO] pr inner join tb_SAIDA sa ON pr.ID_PRODUTO = sa.ID_PRODUTO inner join tb_setor dir ON dir.ID_SETOR = sa.ID_SETOR WHERE substring(NOME_PRODUTO, 15, 1) = 'K' or substring(NOME_PRODUTO, 16,1) = 'L' GROUP BY DIR.NOME_SETOR,sa.DATA_SAIDA ) P ON C.setor = P.setor ) SELECT setor , isnull (preto, 0) Preto, isnull (colorido,0) Colorido, isnull (preto, 0) + isnull (colorido,0) Total FROM TONNER -- View total de Produto mensal CREATE VIEW v_Total_Mensal as SELECT CASE WHEN MONTH(DATA_SAIDA) = 01 THEN 'Janeiro' WHEN MONTH(DATA_SAIDA) = 02 THEN 'Fevereiro' WHEN MONTH(DATA_SAIDA) = 03 THEN 'Março' WHEN MONTH(DATA_SAIDA) = 04 THEN 'Abril' WHEN MONTH(DATA_SAIDA) = 05 THEN 'Maio' WHEN MONTH(DATA_SAIDA) = 06 THEN 'Junho' WHEN MONTH(DATA_SAIDA) = 07 THEN 'Julho' WHEN MONTH(DATA_SAIDA) = 08 THEN 'Agosto' WHEN MONTH(DATA_SAIDA) = 09 THEN 'Setembro' WHEN MONTH(DATA_SAIDA) = 10 THEN 'Outubro' WHEN MONTH(DATA_SAIDA) = 11 THEN 'Novembro' WHEN MONTH(DATA_SAIDA) = 12 THEN 'Dezembro' end Mes, DATEPART(YEAR,GETDATE()) Ano, NOME_SETOR setor, QUANTIDADE Quantidade FROM tb_SAIDA sa INNER JOIN tb_setor dir ON sa.ID_setor = dir.ID_setor
Clique aqui para fazer login e interagir na Comunidade :)