Quinzena no PLSQL

[Fernando.Jarcen]

Olá... preciso gerar um relatório utilizando PLSQL que, o usuário digita um intervalo de datas, por ex: data inicial 01/01/2010, data final 19/04/2010 e a rotina separe as colunas por quinzenas dentro desse intervalo.
No caso,
1Jan 2Jan 1Fev 2Fev 1Mar 2Mar 1Abr 2Abr
25 20 15 10 50 40 20 10

Onde 1Jan é a primeira quinzena de janeiro (1 a 15/01/2010)
2Jan é a segunda quinzena de janeiro (16 a 31/01/2010)
1Fev é a primeira quinzena de fevereiro (1 a 15/02/2010)
2Fev é a segunda quinzena de fevereiro (16 a 28/02/2010)
e assim sussessivamente...

Os dados abaixo do cabeçalho virão de uma função criada, onde são lançados como parâmetros o código de agente e as duas datas e esse método retorna a quantidade de entregas de acordo com esse parâmetro.
Qualquer ajuda será muito bem vinda.
Obrigado.

[Diego_Mello]

Opa...
Fiz alguns testes aqui e saiu essa gambiarra ai...
Só tem que tratar 'algumas' exceções

Selecionar tudo

-- Created on 7/7/2010 by DIEGO_MELLO 
DECLARE
  -- Local variables here
  data_ini DATE := '01-jan-2010';
  data_fim DATE := '20-feb-2010';

  FUNCTION qual_quinzena(p_data DATE) RETURN VARCHAR2 IS
    v_quinzena VARCHAR2(4);
    v_data VARCHAR2(30);
    p_first_day DATE;
    p_last_day DATE;
  BEGIN
    IF TO_CHAR(p_data, 'dd') > 15
    THEN
      v_quinzena := '2';
      v_data := '16' || TO_CHAR(p_data, 'mmrrrr');
      p_first_day := TO_DATE(v_data, 'ddmmrrrr');
      p_last_day := LAST_DAY(p_first_day);
    ELSE
      v_quinzena := '1';
      p_first_day := TRUNC(p_data, 'mm');
      v_data := '15' || TO_CHAR(p_first_day, 'mmrrrr');
      p_last_day := TO_DATE(v_data, 'ddmmrrrr');
    END IF;
  
  
    RETURN v_quinzena || INITCAP(TO_CHAR(p_data, 'mon', 'NLS_DATE_LANGUAGE=PORTUGUESE')) || ' --> ' || p_first_day || ' até ' || p_last_day;
  END qual_quinzena;
BEGIN
  -- Test statements here
  WHILE data_ini <= data_fim
  LOOP
    dbms_output.put_line(qual_quinzena(data_ini));
    data_ini := data_ini + 16;
  END LOOP;
END;

[Fernando.Jarcen]

Caro Diego...
O código funcionou perfeitamente... parabéns...
Agora só falta transpor os dados pois o que é linha deve aparecer como coluna pois as linhas serão preenchidas com o cliente e a quantidade de entregas
Muito obrigado pela ajuda.
Um abraço.
Fernando

[fsitja]

Dá para fazer na extração de dados, no SQL, fica mais simples:

Selecionar tudo

select dt,
       decode(sign(to_char(dt, 'DD') - 16), -1, 1, 2) 
       || to_char(dt, 'Mon', 'NLS_DATE_LANGUAGE=PORTUGUESE') Quinzena_mês
  from tab;

Initcap não é necessário, dá para coloca 'Mon' com maiúscula e já vem corretamente.

Para pegar a quantidade de entregas agrupado, basta fazer um SUM com group by na coluna da quinzena.

Exemplo de execução:

Selecionar tudo

SQL> with tab as (
  2  select to_date('02/01/2010', 'DD/MM/YYYY') dt, 100 qtd_entrega from dual union all
  3  select to_date('15/01/2010', 'DD/MM/YYYY') dt, 250 from dual union all
  4  select to_date('16/01/2010', 'DD/MM/YYYY') dt, 900 from dual union all
  5  select to_date('31/01/2010', 'DD/MM/YYYY') dt, 30 from dual union all
  6  select to_date('09/02/2010', 'DD/MM/YYYY') dt, 2000 from dual union all
  7  select to_date('16/02/2010', 'DD/MM/YYYY') dt, 500 from dual union all
  8  select to_date('28/01/2010', 'DD/MM/YYYY') dt, 700 from dual)
  9  -- fim dos dados de exemplo
 10  select quinzena_mês,
 11         sum(qtd_entrega) sum_qtd_entrega
 12    from (select decode(sign(to_char(dt, 'DD') - 16), -1, 1, 2)
 13                 || to_char(dt, 'Mon', 'NLS_DATE_LANGUAGE=PORTUGUESE') Quinzena_mês,
 14                 qtd_entrega
 15            from tab)
 16   group by quinzena_mês;
 
QUINZENA_mês                                SUM_QTD_ENTREGA
------------------------------------------- ---------------
2Jan                                                   1630
2Fev                                                    500
1Jan                                                    350
1Fev                                                   2000

[Fernando.Jarcen]

Ok amigos... esses códigos funcionam corretamente, parabéns...
Porém o retorno dos dados está invertido:

QUINZENA_mês SUM_QTD_ENTREGA
------------------------------------------- ---------------
2Jan 1630
2Fev 500
1Jan 350
1Fev 2000

nesse caso, as quinzenas tem q ser linhas, assim como a qtde entregas e as quinzenas devem ser colunas.
O complicado é que não sei por ex: se o intervalo for de 1/6/2010 a 8/7/2010 o retorno será em 4 colunas (a primeira coluna do cliente e mais as 3 colunas de quinzenas) agora se for, por ex, 1/1/2010 a 8/7/2010 o retorno deverá ser em 14 colunas(a primeira coluna do cliente mais as 13 colunas de quinzenas).
Pesquisei para gerar a coluna dinamicamente mas não encontrei nada.
A sugestão que me deram é q colocasse num comando for e armazenasse em um cursor os selects para gerar as colunas.
Por favor, me ajudem... é um grande desafio.
Um abraço e obrigado desde já...

[fsitja]

Qual a versão do seu Oracle?

No Oracle 11g tem uma funcionalidade chamada Pivot Query que faz isso automaticamente, bem simples. Porém no 10g e mais antigo tem que fazer no "braço" o pivô, o que se torna um código mais longo.

Como no Oracle o número de colunas tem que ser fixo, a gente teria que fazer quinzena a quinzena a regra por decode ou case expression. Dá para fazer uma pesquisa no google ou aqui no próprio fórum sobre pivô ou pivot. Para podermos dar uma força, precisaria saber como é que você vai determinar o intervalo de datas. É passado dois parâmetros data_início e data_fim? Ou um data_inicio e a data fim é o sysdate?

[Fernando.Jarcen]

Olá...
A minha versão do Oracle é 10g
No caso, se o intervalo a ser gerado na consulta é 1/6/2010 a 15/7/2010 a função tem que gerar os intervalos de quinzena para as linhas do cliente como se segue:
para gerar a primeira coluna

Selecionar tudo

função(cliente,'1/6/2010','15/6/2010')

pra gerar a segunda coluna

Selecionar tudo

função(cliente,'16/6/2010','30/6/2010') 

pra gerar a terceira coluna

Selecionar tudo

função(cliente,'1/7/2010','15/7/2010') 

De acordo com os dois parametros digitados, a função gerará a quantidade de colunas a serem preenchidas com a função acima e os argumentos conforme demonstrados acima.
A função de soma das entregas já tenho pronta com essa denominação:
função(cliente, data-inicio, data-fim).
O unico problema é colocar essa função para gerar select dinamicamente de acordo com o intervalo q será digitado no relatório.
Qualquer ajuda é bem vinda... um abraço...

[Diego_Mello]

Isso é para um relatório?
Pode usar Matrix.

[fsitja]

Daria para fazer assim:

Selecionar tudo

SQL> with tab as (
  2  select to_date('02/01/2010', 'DD/MM/YYYY') dt, 100 qtd_entrega from dual union all
  3  select to_date('15/01/2010', 'DD/MM/YYYY') dt, 250 from dual union all
  4  select to_date('16/01/2010', 'DD/MM/YYYY') dt, 900 from dual union all
  5  select to_date('31/01/2010', 'DD/MM/YYYY') dt, 30 from dual union all
  6  select to_date('09/02/2010', 'DD/MM/YYYY') dt, 2000 from dual union all
  7  select to_date('16/02/2010', 'DD/MM/YYYY') dt, 500 from dual union all
  8  select to_date('28/01/2010', 'DD/MM/YYYY') dt, 700 from dual)
  9  -- fim dos dados de exemplo
 10  SELECT MAX(decode(quinzena_mês, '1Jan', sum_qtd_entrega)) "1Jan",
 11         MAX(decode(quinzena_mês, '2Jan', sum_qtd_entrega)) "2Jan",
 12         MAX(decode(quinzena_mês, '1Fev', sum_qtd_entrega)) "1Fev",
 13         MAX(decode(quinzena_mês, '2Fev', sum_qtd_entrega)) "2Fev"
 14    FROM (SELECT quinzena_mês, SUM(qtd_entrega) sum_qtd_entrega
 15            FROM (SELECT decode(sign(to_char(dt, 'DD') - 16), -1, 1, 2) ||
 16                         to_char(dt, 'Mon', 'NLS_DATE_LANGUAGE=PORTUGUESE') quinzena_mês,
 17                         qtd_entrega
 18                    FROM tab)
 19           GROUP BY quinzena_mês);
 
      1Jan       2Jan       1Fev       2Fev
---------- ---------- ---------- ----------
       350       1630       2000        500

Só que sem o Oracle 11g vai ser preciso "cravar" os meses que você quer...

[Diego_Mello]

Opa...
Como eu disse antes, se for relatório, é mais vantajoso usar Matrix ou Across.
As colunas ficariam na horizontal automaticamente.
Segue a query devidamente ordenada.

Selecionar tudo

SQL> SELECT * FROM (
  2  with tab as (
  3      select to_date('02/01/2010', 'DD/MM/YYYY') dt, 100 qtd_entrega from dual union all
  4      select to_date('15/01/2010', 'DD/MM/YYYY') dt, 250 from dual union all
  5      select to_date('16/01/2010', 'DD/MM/YYYY') dt, 900 from dual union all
  6      select to_date('31/01/2010', 'DD/MM/YYYY') dt, 30 from dual union all
  7      select to_date('09/02/2010', 'DD/MM/YYYY') dt, 2000 from dual union all
  8      select to_date('16/02/2010', 'DD/MM/YYYY') dt, 500 from dual union all
  9      select to_date('28/01/2010', 'DD/MM/YYYY') dt, 700 from dual)
 10      -- fim dos dados de exemplo
 11     SELECT quinzena_mês
 12  	       ,SUM(qtd_entrega) sum_qtd_entrega
 13  				 ,ordem
 14       FROM (SELECT decode(sign(to_char(dt, 'DD') - 16), -1, 1, 2) ||
 15                    to_char(dt, 'Mon', 'NLS_DATE_LANGUAGE=PORTUGUESE') quinzena_mês
 16                   ,qtd_entrega
 17  		             ,max(dt) OVER(PARTITION by DECODE(SIGN(TO_CHAR(dt, 'DD') - 16), -1, 1, 2) || TO_CHAR(dt, 'Mon', 'NLS_DATE_LANGUAGE=PORTUGUESE')) ordem
 18               FROM tab)
 19      GROUP BY quinzena_mês
 20  		        ,ordem
 21  		ORDER BY ordem);
 
QUINZENA_mês                                SUM_QTD_ENTREGA ORDEM
------------------------------------------- --------------- -----------
1Jan                                                    350 15/1/2010
2Jan                                                   1630 31/1/2010
1Fev                                                   2000 9/2/2010
2Fev                                                    500 16/2/2010

[fsitja]

De fato, dei um "chute" já que não teve resposta sobre qual a interface.

[Fernando.Jarcen]

Olá, a minha versão é 10g.
Agradeço a todas as dicas recebidas...
Vou tentar adaptar ao meu código.
No oracle 11g, tem o conceito de pivot que no caso facilitaria pra mim mas como a minha empresa utiliza a 10g, vou tentar utilizar os exemplos para desenvolver o relatório.
Obrigado a todos... Qualquer dúvida, eu posto aqui...
Um grande abraço...
Fernando.Jarcen