Como montar essa SQL?

[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.