Qual a forma correta para esta query

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Boa noite, estou com o seguinte problema:
Tenho que pegar o consumo da movimentação de estoque da seguinte forma:
O usuário irá informar o ano e o mês final para a verificação.
Ex. ano: 2010; até mês 07. Com este parâmetro o usuário esta informando o seguinte: traga o consumo do período 01/01/2010 até 30/07/2010.
O resultado que quero que sai é o seguinte.
Linha por produto e coluna por mês
Ex.

Selecionar tudo

Produto  Mês 1 Mês 2.....Mês 7  total
  1            5        2  ........3         x
  2            5        5          9          x
.
.
.
O problema esta em montar as colunas. Será que ficou claro? Entenderam?

Fiz uma query onde consegui trazer os meses separados, mas como linha.

Selecionar tudo

SELECT to_char(mesano,'MM'),Sum(consumo),e.material
FROM movimento m , cadastro e
WHERE m.codigo_material = e.codigo_material
 GROUP BY to_char(mesano, 'MM'), 
                 to_char(mesano, 'YYYY'),e.material
 HAVING to_char(mesano, 'YYYY') = '2010'
 ORDER BY to_char(mesano, 'MM'),e.material
Alguém tem uma luz?
t+
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 248
Registrado em: Sex, 06 Fev 2009 3:02 pm
Localização: ERECHIM - RS

Olá,
Como vai ser sempre dentro do mesmo ano, sugiro que deixe fixo as 12 colunas dos meses. Neste decode abaixo, o mês que não tiver valores vai aparecer zerado.

Selecionar tudo

round(SUM(decode(to_char(mesano,'MM'),'01',(Consumo),0))) ConsumoMes1
round(SUM(decode(to_char(mesano,'MM'),'02',(Consumo),0))) ConsumoMes2
round(SUM(decode(to_char(mesano,'MM'),'03',(Consumo),0))) ConsumoMes3
round(SUM(decode(to_char(mesano,'MM'),'04',(Consumo),0))) ConsumoMes4
round(SUM(decode(to_char(mesano,'MM'),'05',(Consumo),0))) ConsumoMes5
round(SUM(decode(to_char(mesano,'MM'),'06',(Consumo),0))) ConsumoMes6
round(SUM(decode(to_char(mesano,'MM'),'07',(Consumo),0))) ConsumoMes7
round(SUM(decode(to_char(mesano,'MM'),'08',(Consumo),0))) ConsumoMes8
round(SUM(decode(to_char(mesano,'MM'),'09',(Consumo),0))) ConsumoMes9
round(SUM(decode(to_char(mesano,'MM'),'10',(Consumo),0))) ConsumoMes10
round(SUM(decode(to_char(mesano,'MM'),'11',(Consumo),0))) ConsumoMes11
round(SUM(decode(to_char(mesano,'MM'),'12',(Consumo),0))) ConsumoMes12
espero ter ajudado,
abço,
Marlon
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 248
Registrado em: Sex, 06 Fev 2009 3:02 pm
Localização: ERECHIM - RS

Para ficar um exemplo mais completo:

Selecionar tudo

SELECT e.codigo_material CodigoProduto,
       round(SUM(decode(to_char(mesano,'MM'),'01',(Consumo),0))) ConsumoMes1,
       round(SUM(decode(to_char(mesano,'MM'),'02',(Consumo),0))) ConsumoMes2,
       round(SUM(decode(to_char(mesano,'MM'),'03',(Consumo),0))) ConsumoMes3,
       round(SUM(decode(to_char(mesano,'MM'),'04',(Consumo),0))) ConsumoMes4,
       round(SUM(decode(to_char(mesano,'MM'),'05',(Consumo),0))) ConsumoMes5,
       round(SUM(decode(to_char(mesano,'MM'),'06',(Consumo),0))) ConsumoMes6,
       round(SUM(decode(to_char(mesano,'MM'),'07',(Consumo),0))) ConsumoMes7,
       round(SUM(decode(to_char(mesano,'MM'),'08',(Consumo),0))) ConsumoMes8,
       round(SUM(decode(to_char(mesano,'MM'),'09',(Consumo),0))) ConsumoMes9,
       round(SUM(decode(to_char(mesano,'MM'),'10',(Consumo),0))) ConsumoMes10,
       round(SUM(decode(to_char(mesano,'MM'),'11',(Consumo),0))) ConsumoMes11,
       round(SUM(decode(to_char(mesano,'MM'),'12',(Consumo),0))) ConsumoMes12
FROM movimento m,
     cadastro  e
WHERE m.codigo_material = e.codigo_material
GROUP BY to_char(mesano, 'MM'),
         to_char(mesano, 'YYYY'),e.material
HAVING to_char(mesano, 'YYYY') = '2010'
ORDER BY to_char(mesano, 'MM'),
         e.material 


Att,
Marlon
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Gerou linhas por mês ou seja, gerou linha com valor para a coluna mês 1 e zero para as demais, depois coluna mês 2 e e zero para as demais até 12
Ex.
Vamos supor que o retorno serão 2 linhas.

Selecionar tudo

Produto     mês 1   mês 2
  1               5          0
  2               3          0
  1               0          4
  2               0          5.............
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

o problema estava no group by...aparentemente esta funcionando...preciso colocar mais algumas colunas...qualquer coisa aviso...
Obrigado
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 248
Registrado em: Sex, 06 Fev 2009 3:02 pm
Localização: ERECHIM - RS

é...realmente não tinha percebido. O group by esta errado.

abraço
Marlon
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Tive que fazer uns ajustes na query e onde tinha somente um campo para somar terei que fazer um cálculo onde tenho que tratar com decode, pois é uma divisão e tem momentos que está zero...so que com decode o cálculo fica errado...teria alguma saída ai na sua manga.....
t+
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Pode dar uns exemplo por favor? Tipo: postar o código que você está usando, mostrando o que está retornando, e o que você gostaria que retornasse em vez disso.
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Selecionar tudo

SELECT e.codigo_material CodigoProduto, 
       round(SUM(decode(to_char(mesano,'MM'),'01',((Consumo/Estoque)*30),0))) ConsumoMes1, 
       round(SUM(decode(to_char(mesano,'MM'),'02',((Consumo/Estoque)*30,0))) ConsumoMes2, 
       round(SUM(decode(to_char(mesano,'MM'),'03',((Consumo/Estoque)*30,0))) ConsumoMes3, 
       round(SUM(decode(to_char(mesano,'MM'),'04',((Consumo/Estoque)*30,0))) ConsumoMes4, 
       round(SUM(decode(to_char(mesano,'MM'),'05',((Consumo/Estoque)*30,0))) ConsumoMes5, 
       round(SUM(decode(to_char(mesano,'MM'),'06',((Consumo/Estoque)*30,0))) ConsumoMes6, 
       round(SUM(decode(to_char(mesano,'MM'),'07',((Consumo/Estoque)*30,0))) ConsumoMes7, 
       round(SUM(decode(to_char(mesano,'MM'),'08',((Consumo/Estoque)*30,0))) ConsumoMes8, 
       round(SUM(decode(to_char(mesano,'MM'),'09',((Consumo/Estoque)*30,0))) ConsumoMes9, 
       round(SUM(decode(to_char(mesano,'MM'),'10',((Consumo/Estoque)*30,0))) ConsumoMes10, 
       round(SUM(decode(to_char(mesano,'MM'),'11',((Consumo/Estoque)*30,0))) ConsumoMes11, 
       round(SUM(decode(to_char(mesano,'MM'),'12',((Consumo/Estoque)*30,0))) ConsumoMes12 
FROM movimento m, 
     cadastro  e 
WHERE m.codigo_material = e.codigo_material 
GROUP BY to_char(mesano, 'MM'), 
         to_char(mesano, 'YYYY'),e.material 
HAVING to_char(mesano, 'YYYY') = '2010' 
ORDER BY to_char(mesano, 'MM'), 
         e.material
Para o mês 11 tenho os seguintes registro para o grupo X

Selecionar tudo

CONSUMO	ESTOQUE
7992,01	-7992,01
0	         26640,03
13320,02	 -13320,02
0	         7992,01
0	          -8880,01
para este caso o valor esperado é 144..Percebo quando existe valor 0 o sum da pau.
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 248
Registrado em: Sex, 06 Fev 2009 3:02 pm
Localização: ERECHIM - RS

Acho que é a posição da virgula para separar as sentenças.
tente assim

Selecionar tudo

round(SUM(decode(to_char(mesano,'MM'),'11',((Consumo/Estoque)*30)  ,0))) ConsumoMes11,
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Amigo estou tendo problema na primeira divisão....a query a seguir exemplifica melhor..
a divisão retorna -2. porque?

Selecionar tudo

SELECT Sum(consumo),Sum(estoque),Sum(consumo/estoque),
       round(SUM((Consumo/Estoque)*30)) ConsumoMes11 
FROM 
(
SELECT 7992 Consumo,-7992 estoque
FROM dual
UNION
SELECT 0 Consumo1,26640 estoque
FROM dual
UNION
SELECT 13320 Consumo1,-13320 estoque
FROM dual
UNION
SELECT 0 Consumo1,7992 estoque
FROM dual
UNION
SELECT 0 Consumo1,-8880 estoque
FROM dual
)
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 248
Registrado em: Sex, 06 Fev 2009 3:02 pm
Localização: ERECHIM - RS

se fizer

Selecionar tudo

SELECT Sum(7992/-7992) from dual
teria que dar -1
tem certeza que são estes valores?
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Na verdade se calcular individual é o que você fez....então preciso fazer o sum individual das colunas e depois subtrair para chegar ao valor de 144, onde seria somatoria de consumo = 21312 / estoque = 4440 * dias do mês = 30 resultando em 144...Agora como fazer isso na query do primeiro post, onde preciso da informação mês a mês e transformei linha em coluna, saberia dizer?


21312,03 4440
burga
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Qui, 26 Nov 2009 1:05 pm
Localização: SP
Ricardo H. Tajiri

Amigo estou tendo problema na primeira divisão....a query a seguir exemplifica melhor..
a divisão retorna -2. porque?

Selecionar tudo

SELECT Sum(consumo),Sum(estoque),Sum(consumo/estoque),
round(SUM((Consumo/Estoque)*30)) ConsumoMes11
FROM
(
SELECT 7992 Consumo,-7992 estoque
FROM dual
UNION
SELECT 0 Consumo1,26640 estoque
FROM dual
UNION
SELECT 13320 Consumo1,-13320 estoque
FROM dual
UNION
SELECT 0 Consumo1,7992 estoque
FROM dual
UNION
SELECT 0 Consumo1,-8880 estoque
FROM dual
Está certo retornar -2 pois você está somando o -1 do (7992/-7992) com o -1 do (13320/-13320) com 0 dos outros selects. Os outros selects retornam 0 porque o dividendo é 0. Somando todos resultará em -2.
burga
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Qui, 26 Nov 2009 1:05 pm
Localização: SP
Ricardo H. Tajiri

Na verdade se calcular individual é o que você fez....então preciso fazer o sum individual das colunas e depois subtrair para chegar ao valor de 144, onde seria somatoria de consumo = 21312 / estoque = 4440 * dias do mês = 30 resultando em 144...Agora como fazer isso na query do primeiro post, onde preciso da informação mês a mês e transformei linha em coluna, saberia dizer?
Não sei se entendi bem seu problema, mas acredito que possa ser isto que você está tentando obter:

Selecionar tudo

SELECT CodigoProduto,
       (ConsumoMes1 /EstoqueMes1 )*30 Janeiro,
       (ConsumoMes2 /EstoqueMes2 )*30 Fevereiro,
       (ConsumoMes3 /EstoqueMes3 )*30 Marco,
       (ConsumoMes4 /EstoqueMes4 )*30 Abril,
       (ConsumoMes5 /EstoqueMes5 )*30 Maio,
       (ConsumoMes6 /EstoqueMes6 )*30 Junho,
       (ConsumoMes7 /EstoqueMes7 )*30 Julho,
       (ConsumoMes8 /EstoqueMes8 )*30 Agosto,
       (ConsumoMes9 /EstoqueMes9 )*30 Setembro,
       (ConsumoMes10/EstoqueMes10)*30 Outubro,
       (ConsumoMes11/EstoqueMes11)*30 Novembro,
       (ConsumoMes12/EstoqueMes12)*30 Dexembro
FROM (
  SELECT e.codigo_material CodigoProduto,
         SUM(decode(EXTRACT(MONTH FROM mesano), 1,Consumo,0)) ConsumoMes1,
         SUM(decode(EXTRACT(MONTH FROM mesano), 2,Consumo,0)) ConsumoMes2,
         SUM(decode(EXTRACT(MONTH FROM mesano), 3,Consumo,0)) ConsumoMes3,
         SUM(decode(EXTRACT(MONTH FROM mesano), 4,Consumo,0)) ConsumoMes4,
         SUM(decode(EXTRACT(MONTH FROM mesano), 5,Consumo,0)) ConsumoMes5,
         SUM(decode(EXTRACT(MONTH FROM mesano), 6,Consumo,0)) ConsumoMes6,
         SUM(decode(EXTRACT(MONTH FROM mesano), 7,Consumo,0)) ConsumoMes7,
         SUM(decode(EXTRACT(MONTH FROM mesano), 8,Consumo,0)) ConsumoMes8,
         SUM(decode(EXTRACT(MONTH FROM mesano), 9,Consumo,0)) ConsumoMes9,
         SUM(decode(EXTRACT(MONTH FROM mesano),10,Consumo,0)) ConsumoMes10,
         SUM(decode(EXTRACT(MONTH FROM mesano),11,Consumo,0)) ConsumoMes11,
         SUM(decode(EXTRACT(MONTH FROM mesano),12,Consumo,0)) ConsumoMes12,
         SUM(decode(EXTRACT(MONTH FROM mesano), 1,Estoque,0)) EstoqueMes1,
         SUM(decode(EXTRACT(MONTH FROM mesano), 2,Estoque,0)) EstoqueMes2,
         SUM(decode(EXTRACT(MONTH FROM mesano), 3,Estoque,0)) EstoqueMes3,
         SUM(decode(EXTRACT(MONTH FROM mesano), 4,Estoque,0)) EstoqueMes4,
         SUM(decode(EXTRACT(MONTH FROM mesano), 5,Estoque,0)) EstoqueMes5,
         SUM(decode(EXTRACT(MONTH FROM mesano), 6,Estoque,0)) EstoqueMes6,
         SUM(decode(EXTRACT(MONTH FROM mesano), 7,Estoque,0)) EstoqueMes7,
         SUM(decode(EXTRACT(MONTH FROM mesano), 8,Estoque,0)) EstoqueMes8,
         SUM(decode(EXTRACT(MONTH FROM mesano), 9,Estoque,0)) EstoqueMes9,
         SUM(decode(EXTRACT(MONTH FROM mesano),10,Estoque,0)) EstoqueMes10,
         SUM(decode(EXTRACT(MONTH FROM mesano),11,Estoque,0)) EstoqueMes11,
         SUM(decode(EXTRACT(MONTH FROM mesano),12,Estoque,0)) EstoqueMes12
  FROM movimento m,
       cadastro e
  WHERE m.codigo_material = e.codigo_material
    AND mesano BETWEEN to_date('01/01/2010','dd/mm/yyyy') 
                   AND to_date('31/12/2010','dd/mm/yyyy')
  GROUP BY e.codigo_material)
ORDER BY CodigoProduto;
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Retomando....
Vou testar....mas já estou recebendo outra informação para colocar na query...logo informo....
valeu
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Veja:

Selecionar tudo

SELECT grupo, Sum(consumomes1),Sum(estoqueMes1),
              Sum(consumomes2),Sum(estoqueMes2)
              -- colocar os 12 meses
FROM
(

SELECT e.grupo,

     SUM(decode(to_char(data,'MM'),'01',consumo)) ConsumoMes1,0 estoqueMes1,--forma de fazer 1
     SUM(decode(EXTRACT(MONTH FROM data), 2,consumo,0)) ConsumoMes2,0 estoqueMes2 -- forma de fazer 2
     -- colocar os 12 meses

FROM movimento m,cadastro e
WHERE 		m.cd_material = e.cd_material
	and 	  m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
  and     last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
         to_char(data, 'YYYY'),e.grupo
HAVING
         to_char(data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )

UNION

--estoque
SELECT e.grupo,
       0 consumomes1,SUM(decode(to_char(m.data,'MM'),'01',m.estoque)) EstoqueMes1,
       0 consumomes2,SUM(decode(EXTRACT(MONTH FROM m.data), 2,m.estoque,0)) EstoqueMes2
       -- colocar os 12 meses

FROM  saldo m, cadastro e
WHERE 		m.cd_material = e.cd_material
	and 	  m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
  and     last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
         to_char(m.data, 'YYYY'),e.grupo
HAVING
         to_char(m.data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
ORDER BY 1

)
GROUP BY grupo
ORDER BY 1
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 12 visitantes