Query com dados ponderados.

[Motta 645]

Não creio que precise do UNION pois a ponderação de uma linha é a prórpria linha.

Tentou algo como o post #17 ?

[ahsc 1]

Não creio que precise do UNION pois a ponderação de uma linha é a prórpria linha.

Tentou algo como o post #17 ?

 

SIm, mas não funcionou muito bem.

 

A ponderação que eu tereu que fazer á assim. Exemplo de Produção, Safra e Variedade iguais:

 

 

 

Area | PRODUCAO | SAFRA | VARIEDADE | ESTAGIO | POL | ATR | FIBRA

------------------------------------------------------------------------------------------------------------------------------------------

250 | 3500 | 13/14 | ABCD | 1C | 2222 | 2214 | 5484 Linha1

175 | 2514 | 13/14 | ABCD | 1C | 2548 | 1458 | 9854 Linha2

 

 

Então, na query, eu tenho que analisar se produção, safra e variedade são iguais. Se forem, eu preciso ponderar alguns dados e eliminar, no nosso caso, as duas linhas e deixar somente uma. Para isso eu precisarei fazer esse cálculo.

 

Somar toda a área e deixá-la em um campo

Somar produção

Repetir safra, variedade e estágio

Ponderar POL, ATR e FIBRA. O cálculo fica assim:

 

Como são duas linhas (caso houvessem mais ocorrências, seria necessário adicionar à fórmula abaixo), eu preciso ponderar a primeira linha e somar com a ponderação da segunda linha e dividir o resultado pelo valor total da produção. A fórmula)

 

Fx = (POL_LINHA1 * PRODUCAO_LINHA1) + (POL_LINHA2 * PRODUCAO_LINHA2)

--------------------------------------------------------------------------------------------------------------------

SOMA_PRODUCAO_DAS_DUAS_LINHAS

 

Fazendo isso eu acho o valor ponderado do POL. Esse procedimento deve ser feito para o ATR e FIBRA. Com isso, eu elimino as duas linhas "normais" e adiciono somente a linha ponderada. Fazendo o cálculo o resultado seria mais ou menos esse baseado no exemplo acima.

 

 

Area | PRODUCAO | SAFRA | VARIEDADE | ESTAGIO | POL | ATR | FIBRA

-------------------------------------------------------------------------------------------------------------------------------------------

425 | 6014 | 13/14 | ABCD | 1C | 1194 | 1898 | 7198 Linha1

Depois que as duas linhas foram ponderadas, resta apenas uma linha com o resultado da ponderação. Acho que agora deve ter ficado mais fácil de entender. Se você puder me dar uma luz sobre isso... Não faço a menor ideia de como fazer.

[]s

[Motta 645]

Talvez seja o caso de ter uma tabela totalizadora e uma Procedure ou Programa para povoar isto de tempos em tempos.

 

Numa SP ficaria mais fácil implementar esta regra.

 

O relatório leria esta tabela consolidada

[advaldomesquita 93]

O calculo de cada uma das colunas POL, ATR, FIBRA eh o mesmo do Fx que passou no exemplo?

[ahsc 1]

O calculo de cada uma das colunas POL, ATR, FIBRA eh o mesmo do Fx que passou no exemplo?

 

Sim, exatamente igual.

Talvez seja o caso de ter uma tabela totalizadora e uma Procedure ou Programa para povoar isto de tempos em tempos.

 

Numa SP ficaria mais fácil implementar esta regra.

 

O relatório leria esta tabela consolidada

 

 

O problema é que esse relatório não é gerado pelo cliente em si. O administrador do sistema quem o gera e essa consulta é feita através do Toad For SQL Server. Daí, o administrador gera um arquivo xml e envia para o cliente dele. Desculpe-me a pergunta, mas o que significa SP? :upset:

[advaldomesquita 93]

SP = Stored Procedures. Rotinas.

Estou tentando fazer, mas pensando em um modo dinamico pois se tiver 4 linhas o calculo pode ficar "pesado"

[ahsc 1]

SP = Stored Procedures. Rotinas.

Estou tentando fazer, mas pensando em um modo dinamico pois se tiver 4 linhas o calculo pode ficar "pesado"

 

 

Olha, nas 33 mil linhas que retornaram da consulta, somente resultados de duas linhas repetidas apareceram. Obrigado pela dica do "SP". Não sabia.

 

Vou pensar em uma rotina para deixar essa estrutura dinâmica também.

 

[]s

[advaldomesquita 93]

Veja se os calculos estao corretos:

 

 

declare @table table
(Area float
, PRODUCAO float
, SAFRA varchar(5)
, VARIEDADE varchar(4)
, ESTAGIO varchar(2)
, POL float
, ATR float
, FIBRA float)
 
insert into @table select 250, 3500, '13/14','ABCD','1C',2222,2214,5484 
insert into @table select 175, 2514, '13/14','ABCD','1C',2548,1458,9854 
 
insert into @table select 999, 6666, '14/14','EFGH','2C',1111,2222,3333 
 
select sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, ( (sum(QtdePol)) / (sum(producao))) as POL
, ( (sum(QtdeATR)) / (sum(producao))) as ATR
, ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
from 
(
SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
 
from (
select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM @table a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
) b
) c
group by c.safra, c.variedade, c.estagio 

[Motta 645]

O problema é que esse relatório não é gerado pelo cliente em si. O administrador do sistema quem o gera e essa consulta é feita através do Toad For SQL Server. Daí, o administrador gera um arquivo xml e envia para o cliente dele. Desculpe-me a pergunta, mas o que significa SP? :upset:

 

 

SP - Stored Procedure

O programa é apenas para gravar uma tabela totalizadora, o reletório

faria um "select *" nesta tabela.

[ahsc 1]

 

SP - Stored Procedure

O programa é apenas para gravar uma tabela totalizadora, o reletório

faria um "select *" nesta tabela.

 

A.jr.

 

A forma como você montou o cálculo está certinho sim. Eu vou testar isso. Só preciso entender algumas coisas.

 

Eu vou ter que declarar a tabela como você fez? Imagino que sim por o Motta já deu a dica.

 

A parte do insert imagino que seja para ter algum dado na tabela e à partir dela montar a query, não é?

 

Você montou três queries onde a primeira você monta um cálculo parcial do total dos itens ponderados / pelo total da produção (sum(QtdePol)) / (sum(producao)) e a segunda query você calcula o total ponderado (b.Sum_Pol * b.Producao) as QtdePol e por fim, um select na tabela totalizadora SP para trazer o resultado, é isso?

 

Agora, como é que nessa query, o sistema vai diferenciar registros duplicados quando safra, variedade e estágio forem iguais? Pq essa seria a condição para que montemos a query com essa rotina.

 

[]s

[advaldomesquita 93]

Vou montar um resumo do que fiz e lah posto. Guenta ai....

Resumidamente é isso:

Vamos lá...

Eu montei uma tabela de exemplo (@tabela), mas não precisa no seu caso, somente verifique a performance da consulta.

Onde esta @tabela, troque pela sua consulta mantendo os nomes das colunas.

Note que tenho sim, 3 consultas:

a, b e c! todas em minusculas e uma depende da outra.

Uma analise mais detalhada de cada uma:

A primeira consulta:

select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM @table a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio

Nesta consulta eu somo as colunas POL ATR e FIBRA por linha onde PRODUCAO, SAFRA, VARIEDADE e ESTAGIO foram iguais.

Para isso utilizo o GROUP BY.

Segunda Consulta (b)

SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
FROM (consulta a) -- aqui vai a primeira consulta
) b

Tenho os calculos de POL por PRODUCAO, ainda por linha

e por final a terceira e ultima consulta onde tenho o calculo final que demonstrou:

select sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, ( (sum(QtdePol)) / (sum(producao))) as POL
, ( (sum(QtdeATR)) / (sum(producao))) as ATR
, ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
from  ... (CONSULTA a e CONSULTA b)
 

Se nao entendeu algo, só postar.

[]´s

[ahsc 1]

 

Vou montar um resumo do que fiz e lah posto. Guenta ai....

Resumidamente é isso:

Vamos lá...

Eu montei uma tabela de exemplo (@tabela), mas não precisa no seu caso, somente verifique a performance da consulta.

Onde esta @tabela, troque pela sua consulta mantendo os nomes das colunas.

Note que tenho sim, 3 consultas:

a, b e c! todas em minusculas e uma depende da outra.

Uma analise mais detalhada de cada uma:

A primeira consulta:

select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM @table a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio

Nesta consulta eu somo as colunas POL ATR e FIBRA por linha onde PRODUCAO, SAFRA, VARIEDADE e ESTAGIO foram iguais.

Para isso utilizo o GROUP BY.

Segunda Consulta ( B)

SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
FROM (consulta a) -- aqui vai a primeira consulta
) b

Tenho os calculos de POL por PRODUCAO, ainda por linha

e por final a terceira e ultima consulta onde tenho o calculo final que demonstrou:

select sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, ( (sum(QtdePol)) / (sum(producao))) as POL
, ( (sum(QtdeATR)) / (sum(producao))) as ATR
, ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
from  ... (CONSULTA a e CONSULTA b)
 

Se nao entendeu algo, só postar.

[]´s

 

 

Entendi sim. Vou montar a query que você me mostrou e implementá-la com essa query

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, 
C.DESCRICAO AS NOMEREGIAO, A.* FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

Que é a query principal para esse relatório. Provavelmente amanhã eu te retorne sobre se deu ou não certo. Uma dúvida: com a query que vc montou, ele vai ponderá os dados e trazer os dados restantes?

[advaldomesquita 93]

 

Entendi sim. Vou montar a query que você me mostrou e implementá-la com essa query

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, 
C.DESCRICAO AS NOMEREGIAO, A.* FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

Que é a query principal para esse relatório. Provavelmente amanhã eu te retorne sobre se deu ou não certo. Uma dúvida: com a query que você montou, ele vai ponderá os dados e trazer os dados restantes?

 

Exato! Ele agrupa somente os campos repetidos para calculos. :coolio:

[ahsc 1]

 

Exato! Ele agrupa somente os campos repetidos para calculos. :coolio:

 

Outra dúvida que me surgiu: suponha que eu tenha 3 linhas, eu preciso fazer cálculo de cada uma delas para gerar uma 4 que é a correta e excluir as 3. O group by faz isso? Ele funciona como um looping?

[advaldomesquita 93]

Independende te qtas linhas tiver, 5 ou 50 ele sempre vai primeiro agrupar pelos campos iguais para depois fazer o calculo.

 

O ideial é você fazer o teste com diversos cenários e tudo na mesma tabela.

 

Cria uma tabela a parte e insira alguns registros para verificar, inclusive arredondamentos de campos.

[Motta 645]

Tome cuidado também com DIVIDE BY ZERO em geral um CASE resolve.

[ahsc 1]

 

Exato! Ele agrupa somente os campos repetidos para calculos. :coolio:

 

 

Como eu posso integrar a consulta original com essa consulta que faz o cálculo ponderado?

 

Vou montar um resumo do que fiz e lah posto. Guenta ai....

Resumidamente é isso:

Vamos lá...

Eu montei uma tabela de exemplo (@tabela), mas não precisa no seu caso, somente verifique a performance da consulta.

Onde esta @tabela, troque pela sua consulta mantendo os nomes das colunas.

Note que tenho sim, 3 consultas:

a, b e c! todas em minusculas e uma depende da outra.

Uma analise mais detalhada de cada uma:

A primeira consulta:

select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM @table a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio

Nesta consulta eu somo as colunas POL ATR e FIBRA por linha onde PRODUCAO, SAFRA, VARIEDADE e ESTAGIO foram iguais.

Para isso utilizo o GROUP BY.

Segunda Consulta ( B)

SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
FROM (consulta a) -- aqui vai a primeira consulta
) b

Tenho os calculos de POL por PRODUCAO, ainda por linha

e por final a terceira e ultima consulta onde tenho o calculo final que demonstrou:

select sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, ( (sum(QtdePol)) / (sum(producao))) as POL
, ( (sum(QtdeATR)) / (sum(producao))) as ATR
, ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
from  ... (CONSULTA a e CONSULTA b)
 

Se nao entendeu algo, só postar.

[]´s

 

A consulta que eu tenho para substituir a tabela @tabela é essa

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, 
C.DESCRICAO AS NOMEREGIAO, A.CODUSINA, A.SAFRA, A.ANO, A.MES, A.ITEM, A.CODVARIEDADE AS VARIEDADE, A.CODESTPAD AS ESTAGIO, A.AREA, A.PRODUCAO, A.TCH, A.PCC AS POL, A.ATR, A.FIBRA
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

e me dá esse retorno

 

http://prntscr.com/47p1u2

 

depois, quando eu substituo essa consulta em @tabela, dá esse erro

 

http://prntscr.com/47p2gj

 

Onde eu devo substituir a consulta? Qual operação e errei?

[advaldomesquita 93]

@Motta, para resolver o problema do Zero realmente um case, mas vamos devagar hehehe

 

@ahsc, faça assim:

Na sua consulta, antes do FROM DADVAR A, coloque INTO #temp

E onde tem @tabela, coloque #temp

 

Pode ser que resolva!!!

[ahsc 1]

@Motta, para resolver o problema do Zero realmente um case, mas vamos devagar hehehe

 

@ahsc, faça assim:

Na sua consulta, antes do FROM DADVAR A, coloque INTO #temp

E onde tem @tabela, coloque #temp

 

Pode ser que resolva!!!

 

 

Seria algo mais ou menos assim:

SELECT B.CODIGO, B.NOMEFANT, B.RAZSOC, B.CODREGIAO, 
C.DESCRICAO AS NOMEREGIAO, A.CODUSINA, A.SAFRA, A.ANO, A.MES, A.ITEM, A.CODVARIEDADE AS VARIEDADE, A.CODESTPAD AS ESTAGIO, A.AREA, A.PRODUCAO, A.TCH, A.PCC AS POL, A.ATR, A.FIBRA
INTO #tmp FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO

@A.Jr

 

E onde eu começo o select? No começo do código? Cara, eu faço minhas queries simples. Depois disso tenho que estudar muuuuiiiitttttoooooo. Mas tá valendo, o que vale é aprender.

[advaldomesquita 93]

Como eu falei em alguns posts atras, vamos devagar e por partes. E se eu nao falei, falo agora hehehehe B)

 

Este SELECT você coloca no começo de tudo!!!

mas vamos melhora-lo um pouco e execute somente a parte abaixo:

if object_definition('tempdb..#tmp') is not null
drop table #tmp
 
SELECT B.CODIGO
, B.NOMEFANT
, B.RAZSOC
, B.CODREGIAO
, C.DESCRICAO AS NOMEREGIAO
, A.CODUSINA
, A.SAFRA
, A.ANO
, A.MES
, A.ITEM
, A.CODVARIEDADE AS VARIEDADE
, A.CODESTPAD AS ESTAGIO
, A.AREA
, A.PRODUCAO
, A.TCH
, A.PCC AS POL
, A.ATR
, A.FIBRA
INTO #tmp 
FROM DADVAR A, USINAS B, REGIOES C
WHERE A.SAFRA = '13/14'
AND A.CODUSINA = B.CODIGO
AND C.CODIGO = B.CODREGIAO
 
select sum(c.Area)
, sum(c.producao) 
, c.safra
, c.variedade
, c.estagio 
, ( (sum(QtdePol)) / (sum(producao))) as POL
, ( (sum(QtdeATR)) / (sum(producao))) as ATR
, ( (sum(QtdeFibra)) / (sum(producao))) as Fibra
 
from 
(
SELECT b.Area
, b.Producao
, b.Safra
, b.Variedade
, b.Estagio
, (b.Sum_Pol * b.Producao) as QtdePol
, (b.Sum_ATR * b.Producao) as QtdeATR
, (b.Sum_Fibra * b.Producao) as QtdeFibra
 
from (
select a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
, SUM(a.Pol) AS Sum_Pol
, SUM(a.ATR) AS Sum_ATR
, SUM(a.Fibra) AS Sum_Fibra
FROM #tmp a
GROUP BY a.Area
, a.Producao
, a.Safra
, a.Variedade
, a.Estagio
) b
) c
group by c.safra
, c.variedade
, c.estagio