Existe alguma função para agrupar por intervalo de dias ?

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Sex, 14 Ago 2015 9:12 am

Pessoal, boa tarde.

Gostaria de saber se existe alguma função (Provavelmente Analítica) que possa me ajudar no seguinte problema. Preciso gerar um relatório, onde o usuário irá informar 4 parâmetros (Data Inicial, Quantidade de Dias do Período, Quantidade de Dias entre os Períodos e a Data Final) e o relatório dará a quantidade de registros antes da data inicial e a quantidade de registros de cada período até a data final, exemplo:

Data Inicial: 01/01/2015
Dias do Período: 5
Dias entre os períodos: 2
Data Final: 31/01/2015

Então nesse exemplo, eu precisaria retornar a quantidade de registros antes do dia 01/01/2015, a quantidade de registros de 01/01/2015 até 06/01/2015 (Dt Inicial + 5), a quantidade de registros de 08/01/2015 (6 + 2) até 13/01/2015 e assim até a data final.

Até agora, a única ideia que tivemos, foi de criar uma função que através de um loop, cria-se uma estrutura com os períodos (Semanas) para posteriormente fazer um join com a tabela, mas acredito eu, que se tiver uma função que faça isso, será mais performática do que a solução que pensamos.

Att.
tiago_pimenta
Localização: Barretos / SP

Mensagemem Sex, 14 Ago 2015 12:00 pm

Por acaso esse "Dias do Período = 5" e "Dias entre os períodos= 2" são fixos?
Representam os "dias úteis" e os "finais de semana" respectivamente?
Se sim a consulta ficará bem mais fácil.
DanielNN
Localização: Fortaleza - CE

att,

Daniel N.N.

Mensagemem Sex, 14 Ago 2015 12:44 pm

DanielNN escreveu:Por acaso esse "Dias do Período = 5" e "Dias entre os períodos= 2" são fixos?
Representam os "dias úteis" e os "finais de semana" respectivamente?
Se sim a consulta ficará bem mais fácil.


Daniel,

Serão variáveis, mas a ideia é realmente de ser o período semanal. A planilha que ela nos enviou como exemplo, os períodos começam na segunda e terminam na sexta, mas a última semana, estava com menos dias e foi questionado para ela o porque, ai ela explicou que sempre faz essa planilha na segunda feira de manha, pegando o período da segunda anterior até a sexta, mas que naquela semana em questão, pediram para ela a planilha na sexta de manha, pois teriam uma reunião na sexta a tarde.

Mas eu gostaria de ler o que você pensou.

[ ]s
tiago_pimenta
Localização: Barretos / SP

Mensagemem Sex, 14 Ago 2015 2:50 pm

Fiz a consulta abaixa na qual você informa o período inicial e final e retiro os sábados e domingos.
Acredito que vá lhe atender.

Código: Selecionar todos
SELECT *
  FROM (SELECT dia
             , TO_CHAR(dia, 'd') dia_semana
             , TO_CHAR(dia, 'day') dia_da_semana
          FROM (SELECT data_inicial + LEVEL - 1 dia
                  FROM (SELECT TO_DATE(&DATA_INI, 'DD/MM/RRRR') data_inicial
                          FROM dual)
                CONNECT BY LEVEL <=  TO_DATE(&DATA_FIM, 'DD/MM/RRRR')  - TO_DATE(&DATA_INI, 'DD/MM/RRRR') + 1))
WHERE 1 = 1
  AND dia_semana NOT IN (1,7) /* não É DOMINGO (1) NEM SÁBADO(7) */
;
DanielNN
Localização: Fortaleza - CE

att,

Daniel N.N.

Mensagemem Qua, 19 Ago 2015 7:05 pm

Pessoal,

Vale a pena dar uma olhada no link abaixo:

viewtopic.php?f=2&t=9876&p=38992#p38992

Ver a partir da function Z_TIMESTAMP_DIF.

Vejam também no link abaixo a function NEXT_DAY

http://psoug.org/reference/date_func.html

Abs
Renato Menezes Viana
Localização: Rio de Janeiro - RJ

Mensagemem Seg, 31 Ago 2015 3:23 pm

Pessoal, boa tarde.

Com ajuda do pessoal do grupo Oracle_BR, cheguei até o script abaixo:

Código: Selecionar todos
with periodos as (select 0 num_periodo,
                         to_date('01/01/1901', 'dd/mm/yyyy hh24:mi:ss') dt_ini_periodo,
                         to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') - 1 dt_fin_periodo
                  from dual
                  union
                  select level num_periodo,
                         to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') + ((5 + 2) * (level - 1)) dt_ini_periodo,
                         (to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') + ((5 + 2) * (level - 1)) - 1) + 5 dt_fin_periodo
                  from dual
                  connect by level < trunc((to_date('28/01/2013', 'dd/mm/yyyy hh24:mi:ss') - to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss')) / 5)
                  order by 1 asc)

select bta.tipo_nome,
       pe.dt_ini_periodo || ' - ' || pe.dt_fin_periodo periodo,
       sum(1) total
from bt_amostra_pessoa bap,
     bt_tipo_amostra bta,
     bt_amostra ba,
     periodos pe
where bap.ampe_ativa = 'S'
  and bta.tipo_id = bap.ampe_tipo_id
  and bap.amostra_conferida = 'S'
  and bap.ampe_id = ba.ampe_id
  and bap.ampe_dt_coleta between pe.dt_ini_periodo and pe.dt_fin_periodo
group by bta.tipo_nome,
         pe.dt_ini_periodo || ' - ' || pe.dt_fin_periodo;


Só que agora estou com um outro problema, preciso que as linhas do período se transforme em colunas, ou seja, preciso que o resultado seja assim:

NOME | 01/01/2013 - 05/01/2013 | 07/01/2013 - 11/01/2013
BLAH || 10 || 20

Tentei usando a função PIVOT, mas estou com dificuldade na clausula in do for, tentei colocar o select que está no WITH dentro do in, mas dá erro de ORA-00936. Já usando o PIVOT XML, eu tenho a possibilidade de colocar "ANY" dentro do IN, o que resolveria o problema, pois ele traria todos os resultados, mas ai a saída é em XML, e eu preciso usar esse select em um relatório (Reports).

Alguém tem alguma ideia de como posso contornar esse problema ou de uma outra maneira de fazer esse select acima ?

[ ]s
tiago_pimenta
Localização: Barretos / SP

Mensagemem Seg, 31 Ago 2015 5:14 pm

Pessoal,

Desconsiderem meu último post *rs*, a opção Matriz do Reports faz o que preciso.

[ ]s
tiago_pimenta
Localização: Barretos / SP

Mensagemem Sex, 12 Mai 2017 10:56 am

Bom dia amigos,

antes tarde do que nunca...

Eu estava quebrando a cabeça com PIVOT para passar parâmetro para o FOR ao invés de informações fixas, e só da com PIVOT XML.

Fuçando pela rede e fazendo um monte de testes consegui montar uma rotina.

Não é a coisa mais bonita do mundo mas funcionou.

Segue:

Código: Selecionar todos
create table Tmp_Vendas_Pivot(
Cd_fabr number(3),
dt_emis date,
qt_item number(12));

Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('01/04/2017','dd/mm/yyyy'),20467);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('03/04/2017','dd/mm/yyyy'),53159);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('04/04/2017','dd/mm/yyyy'),59008);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('05/04/2017','dd/mm/yyyy'),52045);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('06/04/2017','dd/mm/yyyy'),52714);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('07/04/2017','dd/mm/yyyy'),51635);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('08/04/2017','dd/mm/yyyy'),21566);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('01/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('03/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('04/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('05/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('06/04/2017','dd/mm/yyyy'),166);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('07/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('08/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('01/04/2017','dd/mm/yyyy'),4405);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('03/04/2017','dd/mm/yyyy'),16213);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('04/04/2017','dd/mm/yyyy'),18545);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('05/04/2017','dd/mm/yyyy'),16103);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('06/04/2017','dd/mm/yyyy'),13802);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('07/04/2017','dd/mm/yyyy'),11159);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('08/04/2017','dd/mm/yyyy'),3770);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('01/04/2017','dd/mm/yyyy'),4420);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('03/04/2017','dd/mm/yyyy'),13574);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('04/04/2017','dd/mm/yyyy'),6120);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('05/04/2017','dd/mm/yyyy'),1004);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('06/04/2017','dd/mm/yyyy'),11133);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('07/04/2017','dd/mm/yyyy'),9693);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('08/04/2017','dd/mm/yyyy'),5840);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('01/04/2017','dd/mm/yyyy'),266);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('03/04/2017','dd/mm/yyyy'),774);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('04/04/2017','dd/mm/yyyy'),854);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('05/04/2017','dd/mm/yyyy'),791);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('06/04/2017','dd/mm/yyyy'),1170);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('07/04/2017','dd/mm/yyyy'),597);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('08/04/2017','dd/mm/yyyy'),76);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('09/04/2017','dd/mm/yyyy'),0);


Código: Selecionar todos
--Declarar a variável
variable v_resultado refcursor 
 
declare
--
v_sql     varchar2(32000);
v_pivot   varchar2(32000);
--
v_dt_inic varchar2(50) := 'to_date('||''''||'01/04/2017'||''''||','||''''||'dd/mm/yyyy'||''''||')';
v_dt_fina varchar2(50) := 'to_date('||''''||'08/04/2017'||''''||','||''''||'dd/mm/yyyy'||''''||')';
--
begin
  --
  select listagg( cd_fabr || ' fab'||lpad(to_Char(cd_Fabr),3,'0'), ',') within group (order by cd_fabr)
  into   v_pivot
  from   (Select distinct cd_fabr from Tmp_Vendas_Pivot);
  --
  v_sql := 'select *
            from ( Select Cd_Fabr, dt_emis, round(Qt_item) qt_item
                   From   Tmp_Vendas_Pivot
                   where  dt_emis between '||v_dt_inic||' and '||v_dt_fina||')
            pivot
            (
             sum(qt_item) it
             for cd_fabr in ('||v_pivot||'))
             where  to_char(dt_emis,'||''''||'d'||''''||') <> 1
             order by dt_emis';
  --
  open :v_resultado for v_sql;
end;
/

-- Imprime o resultado
print v_resultado


Espere que sirva pra alguém.
spernega
Localização: São Paulo - SP



Voltar para PL/SQL

Quem está online

Usuários navegando neste fórum: alextdssouza e 7 visitantes