Menu de navegação

[faccParticipante]

Fiz dessa forma

with TodasVendas as
(Select Loja, Sum(VlrCurso) Total
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 T.Loja, T.Total from TodasVendas T
Order by T.Loja

Funciona, mas preciso realizar mais “contas” (Conforme o SQL no inicio do tópico), como poderia fazer isso?

[fsitjaParticipante]

Da forma como está, mesmo com o with, você necessita de vários table scans para agrupar das várias formas pedidas. Eu pensei em algo mais simples, só que para fazer a referência ao mês passado ficou um pouco mais tenso… 😆

Fiz os seguintes dados de teste, não sei se são válidos, mas é o que pude inventar para testar. A menos que você forneça os comandos de create table e dados de teste a gente fica limitado ao palpite. Só uma sugestão para quando for formular a próxima pergunta, economiza tempo e facilita a qualidade da resposta que você vai obter de nós.

---


CREATE TABLE CYBELAR_CRESCABR
(CONTRATO NUMBER,
VLRCURSO NUMBER,
DTVENDA DATE,
FATURADO VARCHAR2(20),
LOJA VARCHAR2(20));

Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,1000,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,3000,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,25000,to_date('2009/10/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,50000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,50000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,12000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,300,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,250,to_date('2009/10/01','YYYY/MM/DD'),'S','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,500,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,7000,to_date('2009/10/01','YYYY/MM/DD'),'S','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,2000,to_date('2009/10/01','YYYY/MM/DD'),'S','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,1400,to_date('2009/09/01','YYYY/MM/DD'),'N','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,9000,to_date('2009/09/01','YYYY/MM/DD'),'N','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,17000,to_date('2009/10/01','YYYY/MM/DD'),'N','B');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,7999,to_date('2009/10/01','YYYY/MM/DD'),'N','A');
Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,1500,to_date('2009/09/01','YYYY/MM/DD'),'N','A');


editado (copiei e colei o código errado…) 🙄

select t.*,
(vv + contratos) - oracle - pedidos_antigos + pedidos_atuais conferencia
from
(
SELECT trunc(dtvenda, 'MM') dtvenda,
faturado,
c.loja,
nvl(SUM(c.vlrcurso), 0) oracle,
sum(case when length(contrato) = 9
then c.vlrcurso else 0 end) contratos,
lag(sum(c.vlrcurso))
over (order by loja, case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '10/2009'
then 1 else null end, trunc(dtvenda, 'MM')) pedidos_antigos,
lag(sum(c.vlrcurso))
over (order by loja, case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '10/2009'
then 1 else null end, trunc(dtvenda, 'MM') desc) pedidos_atuais
FROM cybelar_crescabr c
GROUP BY loja, trunc(dtvenda, 'MM'), to_char(dtvenda, 'MM/YYYY'), faturado
) t
WHERE to_char(dtvenda, 'MM/yyyy') = '10/2009'
and faturado = 'S'
ORDER BY dtvenda, loja, faturado


O valor da coluna conferência serve para quê? Ele deveria dar zero? Qual a regra dela, porque nos testes que fiz não deu zero, nem na sua versão. Talvez o erro esteja nos meus dados de teste.


LOJA ORACLE VV CONTRATOS PEDIDOS_ANTIGOS PEDIDOS_ATUAIS CONFERENCIA

---

A 7250 250 7000 2900 7999 5099
B 27000 25000 2000 9000 17000 8000


Se assumi alguma premissa incorreta dê um toque, por gentileza.

[]’s
Francisco.

[faccParticipante]

Boa tarde

É exatamente isso, mas alguns resultados não estão sendo exibidos.

Como posso te enviar o Insert? Não quero colocar aqui por causa que a tabela possui o CPF dos clientes.

Imagem do script (Sem modificação)

Como pode notar alguns não estão exibindo o resultado, provavelmente por causa que nem todas as lojas possuem Vendas a Vista (VV), nesses casos como pode ser resolvido?

[fsitjaParticipante]

[quote=”facc”:29u85q60]Boa tarde

É exatamente isso, mas alguns resultados não estão sendo exibidos.

Como posso te enviar o Insert? Não quero colocar aqui por causa que a tabela possui o CPF dos clientes.

Imagem do script (Sem modificação)

Como pode notar alguns não estão exibindo o resultado, provavelmente por causa que nem todas as lojas possuem Vendas a Vista (VV), nesses casos como pode ser resolvido?[/quote]

Desculpa, camarada, eu copiei e colei errado, segue a versão modificada do que fiz:


select t.*,
(vv + contratos) - oracle - pedidos_antigos + pedidos_atuais conferencia
from
(
SELECT trunc(dtvenda, 'MM') dtvenda,
faturado,
c.loja,
nvl(SUM(c.vlrcurso), 0) oracle,
sum(case when length(contrato) = 9
then c.vlrcurso else 0 end) contratos,
lag(sum(c.vlrcurso))
over (order by loja, case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '10/2009'
then 1 else null end, trunc(dtvenda, 'MM')) pedidos_antigos,
lag(sum(c.vlrcurso))
over (order by loja, case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '10/2009'
then 1 else null end, trunc(dtvenda, 'MM') desc) pedidos_atuais
FROM cybelar_crescabr c
GROUP BY loja, trunc(dtvenda, 'MM'), to_char(dtvenda, 'MM/YYYY'), faturado
) t
WHERE to_char(dtvenda, 'MM/yyyy') = '10/2009'
and faturado = 'S'
ORDER BY dtvenda, loja, faturado


Quando falo dados de exemplo, é dado conceitual só, não precisa postar dados sensíveis. Tipo, CPF = 123 é algo válido para usarmos no forum. 8)
Aliás, como CPF não é relevante aqui, pode ser omitido mesmo, como estamos fazendo. Apenas o que você precisa para resolver a situação.
Simplesmente um ponta-pé inicial para ficar mais simples de entender o que está dando errado no caso de teste.

Dá uma olhada nos parênteses da “conferência” está correto ou teria que mudar algo ali?

[faccParticipante]

Valeu amigo,

mas ainda alguns valores não estão sendo exibidos no resultado da conta.

[fsitjaParticipante]

Desculpe, é que não conheço as regras de cálculo, me guiei pelo seu select anterior para formular esse, então ele traz os mesmos resultados. Supondo que usemos os dados de exemplo que postei, quais linhas e quais valores você precisa enxergar na saída do SQL para que esteja correto? Como sei que está errado?

Se precisar modificar ou incluir algum insert para mostrar um caso onde não funcione, à vontade também. 😉

[faccParticipante]

A imagem do meu post mostra o que estou falando, o resultado da conta (Faturados – Oracle – PedAnt + PedAtual) não exibe o resultado, apenas alguns

[fsitjaParticipante]

[quote=”facc”:2xvu2n0b]A imagem do meu post mostra o que estou falando, o resultado da conta (Faturados – Oracle – PedAnt + PedAtual) não exibe o resultado, apenas alguns[/quote]

Ah, foi mal então, não enxerguei que tinha imagem porque o firewall da empresa bloqueia. Vejo à noite em casa.

[faccParticipante]

Muito obrigado.

[fsitjaParticipante]

Olá, fiz umas alterações aqui e rodei o seguinte caso de testes:


SQL> CREATE TABLE CYBELAR_CRESCABR
2 (CONTRATO NUMBER,
3 VLRCURSO NUMBER,
4 DTVENDA DATE,
5 FATURADO VARCHAR2(20),
6 LOJA VARCHAR2(20));

Table created

SQL> begin
2 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,1000,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
3 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,3000,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
4 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (12345678,25000,to_date('2009/10/01','YYYY/MM/DD'),'S','B');
5 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,50000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
6 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,50000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
7 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (123456789,12000,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
8 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,300,to_date('2009/09/01','YYYY/MM/DD'),'S','B');
9 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,250,to_date('2009/10/01','YYYY/MM/DD'),'S','A');
10 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,500,to_date('2009/09/01','YYYY/MM/DD'),'S','A');
11 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,7000,to_date('2009/10/01','YYYY/MM/DD'),'S','A');
12 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,2000,to_date('2009/10/01','YYYY/MM/DD'),'S','B');
13 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (88888888,1400,to_date('2009/09/01','YYYY/MM/DD'),'N','A');
14 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,9000,to_date('2009/09/01','YYYY/MM/DD'),'N','B');
15 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,17000,to_date('2009/10/01','YYYY/MM/DD'),'N','B');
16 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,7999,to_date('2009/10/01','YYYY/MM/DD'),'N','A');
17 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (999999999,1500,to_date('2009/09/01','YYYY/MM/DD'),'N','A');
18 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (555555555,9000,to_date('2009/08/01','YYYY/MM/DD'),'N','B');
19 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (987654321,100,to_date('2009/09/01','YYYY/MM/DD'),'N','X');
20 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (987654321,150,to_date('2009/10/01','YYYY/MM/DD'),'N','X');
21 Insert into "CYBELAR_CRESCABR" ("CONTRATO","VLRCURSO","DTVENDA","FATURADO","LOJA") values (987654321,1150,to_date('2009/10/01','YYYY/MM/DD'),'S','X');
22 end;
23 /

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL> select t.*,
2 (vv + contratos) - oracle - pedidos_antigos + pedidos_atuais conferencia
3 from
4 (
5 SELECT trunc(dtvenda, 'MM') dtvenda,
6 faturado,
7 c.loja,
8 nvl(SUM(c.vlrcurso), 0) oracle,
9 sum(case when length(contrato) = 9
12 then c.vlrcurso else 0 end) contratos,
13 first_value(sum(c.vlrcurso)) over (partition by loja order by
14 case when faturado = 'N'
15 and to_char(dtvenda, 'MM/YYYY')


Segue o SQL apenas, basta mudar o mês de referência para testar nos seus dados.

select t.*,
(vv + contratos) - oracle - pedidos_antigos + pedidos_atuais conferencia
from (select trunc(dtvenda, 'MM') dtvenda,
faturado,
c.loja,
nvl(sum(c.vlrcurso), 0) oracle,
sum(case when length(contrato) = 9
then c.vlrcurso else 0 end) contratos,
first_value(sum(c.vlrcurso)) over (partition by loja order by
case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') < '10/2009'
then 0 else 1 end, trunc(dtvenda, 'MM') desc) pedidos_antigos,
first_value(sum(c.vlrcurso)) over (partition by loja order by
case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '10/2009'
then 0 else 1 end)pedidos_atuais
from cybelar_crescabr c
group by loja, trunc(dtvenda, 'MM'), to_char(dtvenda, 'MM/YYYY'), faturado) t
where to_char(dtvenda, 'MM/yyyy') = '10/2009'
and faturado = 'S'
order by dtvenda, loja, faturado


A ideia basicamente é de usar uma mesma agregação várias vezes, através do CASE para fazer os sums corretos. Para resolver as referências ao mês anterior eu pensei inicialmente em usar um lag, mas como notei que a regra estava elaborada demais, preferi montar uma janela móvel “falsa” no order by com partition by na loja, pegando sempre o mês anterior sem precisar fazer vários scans, apenas dois window sort.

Qualquer problema ou dúvida, é só mandar.

[]’s
Francisco.

[faccParticipante]

Muito Obrigado. Funcionou!

[faccParticipante]

Reabrindo este tópico.

Amigo, boa tarde

Notei um erro no SQL enviado no ultimo tópico. Ele está me exibindo valores do mes que subsequente ao escolhido.

Exemplo. O usuário irá escolher Janeiro / 2010, é para exibir Janeiro E Dezembro, mas está exibindo Janeiro E Fevereiro / 2010 E Dezembro de 2009.

SQL

select t.Loja, t.Oracle, T.vv, t.contratos, t.pedant, t.pedatual,
(vv + contratos) - oracle - pedant + pedatual conferencia
from (select trunc(dtvenda, 'MM') dtvenda,
faturado,
c.loja,
nvl(sum(c.vlrcurso), 0) oracle,
sum(case when length(contrato) = 9
then c.vlrcurso else 0 end) contratos,
first_value(sum(c.vlrcurso)) over (partition by loja order by
case when faturado = 'N' and to_char(dtvenda, 'MM/YYYY') = '12/2009'
then 0 else 1 end) pedant,
first_value(sum(c.vlrcurso)) over (partition by loja order by
case when faturado = 'N'
and to_char(dtvenda, 'MM/YYYY') = '01/2010'
then 0 else 1 end)pedatual
from cybelar_crescabr c
group by loja, trunc(dtvenda, 'MM'), to_char(dtvenda, 'MM/YYYY'), faturado) t
where to_char(dtvenda, 'MM/yyyy') = '01/2010'
and faturado = 'S'

[fsitjaParticipante]

Desculpa mas não consegui entender… aparece fevereiro onde? Em que campo? Inseri uns dados de fevereiro na tabela que postei na página 1 do tópico mas não aconteceu nada. Tem como dar uns inserts de dados de exemplo que façam aparecer esse erro?

Eu notei que há diferenças entre o que eu havia postado anteriormente e o que você está usando agora. Inclusive nas bind variables que passam o mês de referência.

O que mudou nas regras do jogo?

[faccParticipante]

Não mudou nada nas regras.

Descobri que exibe Fevereiro por causa do valor que exibe. Os valores até Janeiro eram inteiros (20,00) e a partir de Fevereiro passou a ser 29,90 e justamente apareceu a soma desse valor.

—— editado ——-
a parte que exibe todos as vendas está OK, achei o problema na parte de trazer os pedidos antigos e atuais. Está dando a impressão de trazer os valores do mês de Fevereiro.

[faccParticipante]

Amigo, peguei o ultimo post que vc passou (copiei e colei no PL/SQL – sem alteração nenhuma), e continua me trazendo os valores de Fevereiro

Está dando a impressão que não está respeitando as datas fornecidas.

[Autor]

Posts