Ajuda com select complicado (RESOLVIDO)

Banco de Dados
#1

Pessoal, se possível, gostaria de uma força com um select um pouco complicado que preciso montar:

São 4 tabelas envolvidas:
campanhas (numero, data_inicial, data_final)
pedidos (numero, data)
produtos_vendidos_no_pedido (pedido, produto, qtde)
produtos_devolvidos_no_pedido (pedido, produto, qtde)

Eu preciso passar, de preferência, apenas o número da campanha como parâmetro.
A saída do select tem que ser esta:
Produto, qtde_vendida, qtde_devolvida (Agrupando por produto e totalizando as 2 qtdes)

A ideia é listar sumarizadamente todos os produtos vendidos e devolvidos que constam nos pedidos cuja data se encaixe entre as datas da campanha fornecida como parâmetro.

Já dei nó nos miolos e ainda não consegui.

Alguém fera no assunto poderia ajudar?

Grato.
Rodrigo

#2

Nào da para testar sem ter as tabelas populadas.Eu criaria uma stored procedure alimentando uma tabela temporária.


SELECT
  PV.PRODUTO
  SUM(PV.QTDE_VENDIDA), 
  SUM(PD.QTDE_DEVOLVIDA)

FROM 
  PEDIDO P 
    INNER JOIN PRODUTOS_VENDIDOS PV     ON PV.PEDIDO=P.NUMERO 
    INNER JOIN PRODUTOS_DEVOLVIDOS PD ON PD.PEDIDO=P.NUMERO

WHERE 
  P.DATA >= (SELECT DATA_INICIAL FROM CAMPANHA WHERE NUMERO=[CAMPANHA]) 
  AND P.DATA <=(SELECT DATA_FINAL FROM CAMPANHA WHERE NUMERO=[CAMPANHA])}

GROUP BY
  PV.PRODUTO
#3

Haroldo, primeiramente obrigado pela resposta tão rápida.

Eu testei a query e deu uma pequena diferença nos valores, contando manualmente as qtdes. Como são poucos registros dá pra contar manual pra conferir.
Adaptando a query para o nome real dos campos ficou assim:

SELECT PV.produto, SUM( PV.qtde ) , SUM( PD.qtde )
FROM pedidos P
INNER JOIN pedidos_itens_atuais PV ON PV.pedido = P.pedido
INNER JOIN pedidos_itens_devolvidos PD ON PD.PEDIDO = P.pedido
WHERE P.data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = [campanha] )
AND P.data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = [campanha] )
GROUP BY PV.produto

A questão é que nem sempre todos os produtos constantes na tabela de produtos vendidos (pedidos_itens_atuais) estão também na tabela de produtos devolvidos (pedidos_itens_devolvidos). Acho que por isso a somatória ficou diferente.
Talvez eu tenha que usar uma quinta tabela (tabela de produtos) pra ter certeza de que todos os produtos foram lidos, algo assim.

Na minha contagem manual ficou assim:
Produto/Vendidos/Devolvidos
1 57 24
2 67 48
3 31 1
4 14 5

Pela query ficou assim:
produto SUM( PV.qtde ) SUM( PD.qtde )
1 80 43
2 64 36
3 35 67
4 29 51

De qq forma já serviu como ponto de partida até pra estudo meu de como funciona o inner join, pois não tem jeito de eu aprender esse troço.
Caso tenha algum tempo e possibilidade de dar uma nova olhada, postei o dump das tabelas aqui: http://www.softideias.com/clarajoias/sql.txt. As contagens que citei acima se referem a campanha de número 4.

Muito obrigado mesmo.
Rodrigo

#4

Este procedimento vai ser rotineiro ou esporádico?

#5

É uma consulta que vai ser realizada praticamente uma vez por mês, no final da campanha de vendas.

#6

SELECT PV.produto as produto , SUM( PV.qtde ) as vendido , 0 as devolvido FROM pedidos P INNER JOIN pedidos_itens_atuais PV ON PV.pedido = P.pedido WHERE P.data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = [campanha] ) AND P.data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = [campanha] ) UNION ALL SELECT PD.produto as produto , 0 as vendido , SUM( PD.qtde ) as devolvido FROM pedidos P INNER JOIN pedidos_itens_devolvidos PD ON PD.PEDIDO = P.pedido WHERE P.data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = [campanha] ) AND P.data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = [campanha] ) GROUP BY PV.produto

#7

tenta assim, se não der vamos criar uma view com esse código sem o group by.
e fazer o select em cima da view.

#8

nesse caso é rotineira, temos que desenvolver algo definitivo.

#9

Haroldo, com a última query deu o seguinte erro no phpmyadmin:
#1054 - Unknown column ‘PV.produto’ in ‘group statement’

Acredito que o group by dá erro pq PV.produto só existe no primeiro select do UNION, no segundo ele se chama PD.produto.

Nesse meio tempo, fiz em paralelo uma tabela temporária e meio que preenchi a contagem via apl blank, funcionou belezinha.
Como vc queria criar uma view, acho que acabei optando por uma solução parecida, só que via blank.

De qq forma achei a query interessante pra estudo.

Muito obrigado mesmo pelo empenho na solução.
Rodrigo

#10

tente trocar PV.produto por produto apenas.

#11

Seguindo o raciocínio do mestre Haroldo:

SELECT PV.produto, SUM( PV.qtde )
FROM pedidos P
INNER JOIN pedidos_itens_atuais PV ON PV.pedido = P.pedido
WHERE P.data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = 4 )
AND P.data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = 4 )
GROUP BY PV.produto
UNION ALL
SELECT PD.produto, SUM( PD.qtde )
FROM pedidos P
INNER JOIN pedidos_itens_devolvidos PD ON PD.pedido = P.pedido
WHERE P.data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = 4 )
AND P.data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = 4 )
GROUP BY PD.produto

Está query está contando de acordo com sua contagem manual, com o detalhe de mostrar o agrupamento abaixo do outro e não ao lado.

#12

Haroldo, fiz uma pequena mudança na sua query, adicionando um group by em cada select do union:

SELECT PV.produto AS produto, SUM( PV.qtde ) AS vendido, 0 AS devolvido
FROM pedidos P
INNER JOIN pedidos_itens_atuais PV ON PV.pedido = P.pedido
WHERE P.data_pedido >= (
SELECT data_inicial
FROM campanhas
WHERE numero =4 )
AND P.data_pedido <= (
SELECT data_final
FROM campanhas
WHERE numero =4 )
GROUP BY PV.produto
UNION ALL
SELECT PD.produto AS produto, 0 AS vendido, SUM( PD.qtde ) AS devolvido
FROM pedidos P
INNER JOIN pedidos_itens_devolvidos PD ON PD.PEDIDO = P.pedido
WHERE P.data_pedido >= (
SELECT data_inicial
FROM campanhas
WHERE numero =4 )
AND P.data_pedido <= (
SELECT data_final
FROM campanhas
WHERE numero =4 )
GROUP BY PD.produto

A contagem bateu 100%, só que vendidos e devolvidos ficaram em blocos separados:

produto vendido devolvido
1 57 0
2 67 0
3 31 0
4 14 0
1 0 24
2 0 48
3 0 1
4 0 5
5 0 6

O lance agora era juntar os 2 blocos num bloco só.

Se eu tentar o group by único no final usando “GROUP BY” produto, ele soma as qtdes vendidas e totaliza no ultimo produto:
produto vendido devolvido
4 169 0
1 0 24
2 0 48
3 0 1
4 0 5
5 0 6

#13

Cleyton, foi exatamente o que fiz.
Pensando melhor, acho que dessa forma um grid com quebra totalizada por produto já resolve o problema né?

Concorda Haroldo?

#14

O caminho acho que é por aqui:

SELECT pedidos_itens_atuais.produto, (SELECT SUM( qtde ) FROM pedidos_itens_atuais WHERE pedido = pedido) AS Vendido, (SELECT SUM( qtde ) FROM pedidos_itens_devolvidos WHERE pedido = pedido) AS devolvido
FROM pedidos_itens_atuais, pedidos_itens_devolvidos, pedidos
WHERE data_pedido >= ( SELECT data_inicial FROM campanhas WHERE numero = 4 )
AND data_pedido <= ( SELECT data_final FROM campanhas WHERE numero = 4 )
GROUP BY produto

Está agrupando, agora é ajeitar para contar corretamente. algo me diz que o pulo do gato está nas cláusulas where das subselects.

#15

Cleyton, não ficou legal não, olha o resultado:

produto Vendido devolvido
1 323 98
2 323 98
3 323 98
4 323 98
5 323 98
16 323 98

Apareceu até um produto que não foi vendido nem devolvido.
Acho que o resultado do penúltimo post meu ficou melhor, veja lá.

#16

Melhor forma é criando uma view mesmo, tive que importar sua base para testar.
Crie a View no banco:

[code]CREATE VIEW view_vendas AS

SELECT PV.produto as produto , sum(PV.qtde) as vendido , sum(0) as devolvido , C.Numero as Campanha
FROM pedidos P
INNER JOIN pedidos_itens_atuais PV ON PV.pedido = P.pedido
INNER JOIN campanhas C on P.data_pedido >= C.data_inicial and P.data_pedido <= C.data_final
group by campanha, produto
UNION all
SELECT PD.produto as produto , sum(0) as vendido , sum(PD.qtde) as devolvido, C.Numero as Campanha
FROM pedidos P
INNER JOIN pedidos_itens_devolvidos PD ON PD.PEDIDO = P.pedido
INNER JOIN campanhas C on P.data_pedido >= C.data_inicial and P.data_pedido <= C.data_final
group by campanha, produto;[/code]

Select da consulta:

SELECT produto, sum(Vendido), sum(devolvido), campanha FROM view_vendas where campanha=4 group by campanha, produto;

#17

Outra forma é não colocar a campanha no select principal, no filtro criar uma campo manualmente para selecionar a campanha, na onscriptinit fazer um sc_lookup na campanha, pegar as datas e adicionar dinamicamente no where as datas inicial e final na critica de pedidos.

#18

A view ficou perfeita Haroldo, tópico solucionado!

Muitíssimo obrigado.
Rodrigo