Ajuda com Sql Complexo

12 de janeiro de 2012 3 respostas

mixulidio 12 de janeiro de 2012

Olá,
Preciso fazer um calculo: Produtos Bons * 100.0 / (Produtos Ruins + Produtos Bons).
De um determinado Produto em um período…
Os Produtos Bons e ruins são diferenciados pelo campo status = 0 é bom, 1 = ruin,
estão na mesma tabela e as vezes eles estão repetidos, deve ser descartado aqueles que tiverem o mesmo serial e status…

com uma ajuda eu consegui fazer isso:

SELECT cod_prod, 
     (select count(distinct serial) FROM tb_registro
        WHERE cod_status = 1 and datah BETWEEN TO_timestamp('10/01/2012 00:00:00','DD/MM/YYYY HH24:MI:SS') 
           AND TO_timestamp('10/01/2012 23:59:59','DD/MM/YYYY HH24:MI:SS') and cod_prod LIKE '%000337%') as total_nok,
     SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) * 100.0 /
         ((select count(distinct serial) FROM tb_registro  WHERE cod_status = 1 
            and datah BETWEEN TO_timestamp('01/01/2012 00:00:00','DD/MM/YYYY HH24:MI:SS')
            AND TO_timestamp('01/03/2012 23:59:59','DD/MM/YYYY HH24:MI:SS') and cod_prod LIKE '%000337%')  + 
            SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) ) as yield, 
     count(distinct serial) AS count_serial, SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) AS qt_bons, SUM(1) AS qt_total,
     to_char(datah, 'DD/MM/YY') AS dia 
FROM tb_registro 
WHERE datah BETWEEN TO_timestamp('01/01/2012 00:00:00','DD/MM/YYYY HH24:MI:SS') 
                AND TO_timestamp('01/03/2012 23:59:59','DD/MM/YYYY HH24:MI:SS') 
                and cod_prod LIKE '%000337%' 
GROUP BY cod_prod , to_char(datah, 'DD/MM/YY')  ORDER BY cod_prod, to_char(datah, 'DD/MM/YY')

Para somente um dia funciona: ex:10/01/12:
“000337”;3;98.9090909090909091;274;272;275;“10/01/12”

Resultado, mas para mais de um dia: o valor da subconsulta fica fixado em 3, veja a diferença na porcentagem em negrito:
“000337”;3;81.7777777777777778;191;184;191;"09/01/12"
“000337”;3;86.9009584664536741;274;272;275;"10/01/12"
“000337”;3;88.7671232876712329;350;324;356;“11/01/12”

A Subconsulta está errada, ela mostra o mesmo resultado para todas as linhas, gostaria que fosse individual para cada registro retornado.
Existe alguma maneira mais simples de ser feito sem usar subconsulta?
A dificuldade é encontrar algum “distinct” que pudesse fazer a distinção de dois campos ao mesmo tempo ex:
Serial;Status:
A;0
A;0 <<duplicado (unir) =1reg.
A;1
B;1
B;1
B;1 <<duplicado
C;0

Contagem: A0 = 1; A1 = 1; B1 = 1… Total: 2bons e 2ruins.

Abraço, qalquer dúvida sobre a esplicação pergunta aí…
valew

3 Respostas

mixulidio 13 de jan. de 2012

Consegui melhor significamente com: count(distinct(serial,cod_status))

SELECT
cod_prod,
to_char(datah, 'DD/MM/YY'),
count(distinct serial) AS produzidos,
SUM(1) AS registros,
count(distinct(serial,cod_status)) AS total,
SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) AS OK,
count(distinct(serial,cod_status)) - SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) as NOK,
SUM(CASE WHEN cod_status = 0 THEN 1 ELSE 0 END) * 100.0 / count(distinct(serial,cod_status)) as yield

FROM tb_registro WHERE
datah BETWEEN TO_timestamp('09/01/2012 00:00:00','DD/MM/YYYY HH24:MI:SS') 
                AND TO_timestamp('13/01/2012 23:59:59','DD/MM/YYYY HH24:MI:SS') 
                and cod_prod LIKE '%%' 
GROUP BY cod_prod , to_char(datah, 'DD/MM/YY')
ORDER BY cod_prod, to_char(datah, 'DD/MM/YY')

só que agora o calculo algumas vezes me retorna negativo…

“000356”;“12/01/12”;2;4;2;4;-2;200.0000000000000000
"000357";“10/01/12”;49;56;55;50;5;90.9090909090909091

mixulidio 13 de jan. de 2012

SELECT
	cod_prod,
	to_char(datah, 'DD/MM/YY'),
	SUM(1) AS registros,
	count(distinct serial) AS produzidos,
	count(distinct(serial,cod_status)) AS total,
	count(distinct(serial,cod_status)) - count(distinct serial) as NOK,
	count(distinct serial) * 100.0 / count(distinct(serial,cod_status)) as yield
FROM tb_registro WHERE
	datah BETWEEN TO_timestamp('09/01/2012 00:00:00','DD/MM/YYYY HH24:MI:SS') 
			AND TO_timestamp('13/01/2012 23:59:59','DD/MM/YYYY HH24:MI:SS') 
			AND cod_prod LIKE '%001057%' 
GROUP BY cod_prod, to_char(datah, 'DD/MM/YY')
ORDER BY cod_prod, to_char(datah, 'DD/MM/YY')

“001057”;“09/01/12”;180;181;181;1;99.4475138121546961
"001057";“10/01/12”;250;255;250;0;100.0000000000000000
"001057";“11/01/12”;240;240;240;0;100.0000000000000000
"001057";“12/01/12”;259;264;259;0;100.0000000000000000
"001057";“13/01/12”;10;10;10;0;100.0000000000000000

mixulidio 13 de jan. de 2012

Porém os cálculos continuam incorretos para algumas ocasiões…

Serial ; Status
A; 0
A; 0
B; 0
B; 1
C; 0
C; 1
D; 1

Neste ultimo SQL, para esta tabela o serial ‘D’ não é contabilizado.
Como calcular todos os Serials distintos(serial,status) com o status 1??
algo do tipo:
SUM(CASE WHEN cod_status = 1 THEN 1 ELSE 0 END) --não pegaria os distintos, duplicaria o resultado e,
count(distinct(serial,cod_status)) – não pega somente os status=1

Obrigado, fico no aguardo de sugestões dos amigos

Valew!

Criado 12 de janeiro de 2012

Ultima resposta 13 de jan. de 2012

Respostas 3

Participantes 1

3 Respostas

Topicos relacionados