Menu de navegação

[faccParticipante]

Boa tarde, tenho uma dúvida em relação a montagem de uma SQL para exibir o seguinte relatório.

Loja – Todos os Dados – A Vista – Contratos – Pedidos Anteriores – Pedidos Atuais – Diferença
L01 – 2525,00 – 25,00 – 2500,00 – 0,00 – 0,00 – 0,00
L02 – 1300,00 – 0,00 – 1325,00 – 25,00 – 0,00 – 0,00

Algumas particularidades:

1 – Os A vista e Contratos são apenas os faturados do mês informado
2 – Todos os dados não faz diferença entre os faturados e não faturados
3 – Pedidos Anteriores: São os vendidos no mês anterior e faturados no mês atual
4 – Pedidos Atuais: São os faturados e vendidos no mês atual
5 – A diferença é calculada da seguinte forma:
(A Vista + Contratados) – Todos – Pedidos Anteriores + Pedidos Atuais

Cheguei na seguinte conclusão:

select C.LOJA,
nvl(sum(C.VlrCurso), 0) Oracle,
nvl((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'S'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
AND LENGTH(CONTRATO) < 9 AND C.LOJA = LOJA GROUP BY LOJA), 0) VV, nvl((SELECT SUM(VLRCURSO) FROM CYBELAR_CRESCABR WHERE FATURADO = 'S' AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009' AND LENGTH(CONTRATO) >= 9
AND C.LOJA = LOJA
GROUP BY LOJA),
0) CONTRATOS,
nvl((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'N'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '09/2009'
AND C.LOJA = LOJA
GROUP BY LOJA),
0) PEDIDOS_ANTIGOS,
nvl((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'N'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
AND C.LOJA = LOJA
GROUP BY LOJA),
0) PEDIDOS_ATUAIS,
(((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'S'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
AND LENGTH(CONTRATO) < 9 AND C.LOJA = LOJA GROUP BY LOJA) + (SELECT SUM(VLRCURSO) FROM CYBELAR_CRESCABR WHERE FATURADO = 'S' AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009' AND LENGTH(CONTRATO) >= 9
AND C.LOJA = LOJA
GROUP BY LOJA)) - SUM(C.VLRCURSO) -
(SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'N'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '09/2009'
AND C.LOJA = LOJA
GROUP BY LOJA) + (SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'N'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
AND C.LOJA = LOJA
GROUP BY LOJA)) CONFERENCIA
from cybelar_crescabr C
where Faturado = 'S'
and to_char(dtvenda, 'MM/yyyy') = '10/2009'
group by Loja
ORDER BY LOJA


Mas acredito que tenha uma forma mais fácil de se fazer.
Alguem poderia me dar uma luz?

[fsitjaParticipante]

Tem como você postar uns dados de exemplo da tabela “cybelar_crescabr” para testar? Facilitaria muito para tentar algo.

[faccParticipante]

Segue a imagem dos dados

[Leonardo LitzParticipante]

Ola FACC,

Sem considerar os indices que vc tem ai.
Olhando por cima, da para dizer que os groups by contidos nas subselects das tuplas podem ser retirados, voce ja esta filtrando por loja, so ira retornar uma loja, nao tem o pq agrupar, isso ja aumentaria a performace.
Outra coisa que pode lhe ajuda eh substituindo:

AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'

POR

AND DTVENDA BETWEEN TO_DATE('01/10/2009,'DD/MM/YYYY') AND LAST_DAY(TO_DATE('01/10/2009,'DD/MM/YYYY'))


Outra coisa, na parte abaixo, voce esta separando contratos com menos de 9 digitos de com mais de 9 digitos, para depois somar… nao seria melhor nem separar????


(((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'S'
AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
AND LENGTH(CONTRATO) = 9
AND C.LOJA = LOJA
GROUP BY LOJA)


Ficaria assim:


(((SELECT SUM(VLRCURSO)
FROM CYBELAR_CRESCABR
WHERE FATURADO = 'S'
AND DTVENDA BETWEEN TO_DATE('01/10/2009,'DD/MM/YYYY') AND LAST_DAY(TO_DATE('01/10/2009,'DD/MM/YYYY'))
AND C.LOJA = LOJA )


Tem bastante coisa que pode ser melhorada neste codigo, mas para isso devo entender melhor sua regra e estrutura, essa sao algumas que vi de relance.

[faccParticipante]

Bom dia, obrigado pelas dicas.

A respeito do contrato com mais e menos digitos, aqui na empresa eles adotaram contratos com menos que 9 digitos sao vendas a vista e os com 9 ou mais sao a prazo, ou seja, preciso realmente separar, pensei em usar um case, mas como poderia ficar?

[Leonardo LitzParticipante]

Entao FACC, na coluna em que vc soma todos os dados (a vista + contratos), voce nao precisa dividir quais sao os contratos de menos de 9 digitos e mais de 9 digitos, voce pode trazer os dois juntos de uma so vez.
Outra coisa, voce pode colocar estes dados em uma subselect no from, e reutilizar a informacao.

Vlw Leonardo Litz

[faccParticipante]

é eu percebi isso.

Vc poderia me mandar um exemplo de como colocar subselects no from?

[burgaParticipante]

Utilize o comando WITH pra eliminar estas subquerys repetidas, agrupando, dentro do with, também pela coluna da loja, pra voê poder usar depois…

Ajuda bastante! 😀

[faccParticipante]

Pode me enviar um exemplo?

[burgaParticipante]

O comando WITH cria uma ou mais espécies de “tabelas tempoárias” pra você, assim você pode consultar direto nestas “tabelas”, no exemplo abaixo eu to criando a tabela TAB1 e fazendo um select nela depois… Entra na mesma categoria de colocar a subqery na cláusula from que o Litz comentou.


WITH
TAB1
AS
(SELECT LOJA, SUM(VLRCURSO) TOTAL
FROM CYBELAR_CRESCABR
WHERE AND TO_CHAR(DTVENDA, 'MM/YYYY') = '10/2009'
GROUP BY LOJA)
SELECT
* FROM TAB1 WHERE TOTAL > 2;


[burgaParticipante]

Segue alguns links pra você entender melhor a cláusula WITH…

http://www.dba-oracle.com/t_with_clause.htm

http://www.oracle-base.com/articles/misc/WithClause.php

[faccParticipante]

Funciona para a mesma tabela?

Pelos exemplos ele cita tabelas diferentes.

[faccParticipante]

Cheguei nisso, mas tá em um loop infinito… onde Errei?

with
Sum_Oracle as
(select Nvl(sum(VlrCurso), 0) Oracle from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
and Length(Contrato) = 9
Group by Loja),
Sum_Contratos as
(Select Nvl(Sum(VlrCurso), 0) Contratos from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
and Length(Contrato) >= 9
Group by Loja),
Sum_Anterior as
(Select Nvl(Sum(VlrCurso), 0) PedAnt from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/09/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/09/2009', 'DD/MM/YYYY'))
Group by Loja),
Sum_Atual as
(Select Nvl(Sum(VlrCurso), 0) PedAtual from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
Group by Loja)
Select Loja
from Cybelar_Crescabr,
Sum_Oracle,
Sum_VV,
Sum_Contratos,
Sum_Anterior,
Sum_Atual
where Faturado = 'S'
and DtVenda between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
group by Loja
Order by Loja

E como faço para trazer o resultado da diferença entre eles?

[burgaParticipante]

[quote=”facc”:1clykax5]Cheguei nisso, mas tá em um loop infinito… onde Errei?

with
Sum_Oracle as
(select Nvl(sum(VlrCurso), 0) Oracle from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
and Length(Contrato) = 9
Group by Loja),
Sum_Contratos as
(Select Nvl(Sum(VlrCurso), 0) Contratos from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
and Length(Contrato) >= 9
Group by Loja),
Sum_Anterior as
(Select Nvl(Sum(VlrCurso), 0) PedAnt from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/09/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/09/2009', 'DD/MM/YYYY'))
Group by Loja),
Sum_Atual as
(Select Nvl(Sum(VlrCurso), 0) PedAtual from Cybelar_Crescabr
where Faturado = 'S'
and DtVenda Between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
Group by Loja)
Select Loja
from Cybelar_Crescabr,
Sum_Oracle,
Sum_VV,
Sum_Contratos,
Sum_Anterior,
Sum_Atual
where Faturado = 'S'
and DtVenda between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
group by Loja
Order by Loja

E como faço para trazer o resultado da diferença entre eles?[/quote]

Bom, primeiro que SUM_VV e SUM_CONTRATOS são exatamente iguais, você pode eliminar uma delas.

Depois, analisando o select principal
Select Loja
from Cybelar_Crescabr,
Sum_Oracle,
Sum_VV,
Sum_Contratos,
Sum_Anterior,
Sum_Atual
where Faturado = 'S'
and DtVenda between To_Date('01/10/2009', 'DD/MM/YYYY')
and Last_Day(To_Date('01/10/2009', 'DD/MM/YYYY'))
group by Loja
Order by Loja
Você está fazendo um cross join de todas as “tabelas temporárias” com a tabela cybelar_crescabr.

O mais correto seria, nos selects das “tabelas temporárias”, você retornar também o codigo da loja pra fazer o join corretamente entre os registros no select principal.

E ainda, retornando o codigo da loja nos outros selects elimina a necessidade de usar a tabela cybelar_crescabr no select principal, pois você terá todos os dados de que necessita nas “tabelas temporárias”.

Mais, no select principal está faltando você imprimir os campos de que deseja (fazer a projeção), provenientes das “tabelas temporárias”.

Refaça o select com estas correções e veja se dá certo… 8)

[faccParticipante]

Bom dia,

Não são iguais, pois Necessito trazer as vendas a Vista e a Prazo por isso que tem uma condição “Contratos = 9”

Vc poderia em enviar um exemplo básico?

[Autor]

Posts