Cálculo tempo total intervalos

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
grimaldo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qua, 27 Jun 2012 1:55 pm

Caros, solicito a ajuda do grupo no problema abaixo:

1) Tenho uma tabela que registra o tempo de indisponibilidade de aplicações que trabalho.

2) Tenho a necessiade de registrar por quanto tempo a indisponibilidade esteve ocorrendo, ex:

Selecionar tudo

_________1   2    3    4    5   6    7   8______________________________  Janela madrugada e manhã
e-mail   **********
portal       **************** 
rede              ******
erp                                  *****

Ou seja Email ficou indfisponivel de 1 hora da manhã até 3 horas da manhã, portal de 2 até 5 horas, rede de 3 a 4 horas e erp de 7 a 8 da manhã

Então necessito de uma query, procedure ou função que retorne que o tempo total foi de 5 horas, ou seja, iniciou às 01 da manha com e-mail e se propagou até 05 com portal, mas retornou as 7 e foi até 8 horas com erp.


Portanto colegas, intervalos que estão já apropriados em outros intervalos devem ser desconsiderados, o intervalo da rede foi desconsiderado, pois o tempo da indisponibilidade deste intervalo já estava inserida no intervalo de portal.

É isso colegas, aguardo sugestões, muito obrigado.

Grimaldo
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Olá, tem como você postar a estrutura simplificada da tabela, com suas colunas e exemplos de dados para teste?

Esse exemplo que você postou não deu pra entender... tente postar código sempre entre tags de CODE que ajuda também.
grimaldo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qua, 27 Jun 2012 1:55 pm

OK Colega, sou novo no grupo, segue exemplo da tabela:

Ex:

Selecionar tudo

           Servico    Data Inicio                      Data Fim  
            E-mail    10/05/2012 10:30:20      10/05/2012  11:40:20
            Portal     10/05/2012 10:40:30      10/05/2012  13:40:50
            Rede      10/05/2012 13:00:00      10/05/2012  16:20:10

Estes são os campos, espero que vocês possam ajudar, obrigado.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Quais são os limites das janelas? Uma janela é igual a um dia?

Ou há várias por dia? Exemplo: cada dia pode ter 3 janelas onde uma é da meia-noite às 7:59h, outra das 8h às 17:59 por exemplo, e outra da 18h até às 11:59... e assim por diante.

Você gostaria de ver os intervalos em janelas por dia? Por exemplo: dia 10/5, dia 11/5 em linhas separadas?

Pode acontecer do campo Data Início ser 10/5/2012 10:30:00 e a Data Fim da mesma linha ser no dia seguinte, por exemplo ter 24 horas de indisponibilidade e voltar apenas às 10:30:00 do dia 11/5/2012, e "invadir" outra janela?

Nesse caso tem que fazer uma divisão da disponibilidade de meia-noite - 10:30:00 no dia 10 e outra linha com meia-noite até 10:30:00 do dia 11, ou manter tudo na mesma linha?

Parece ser um algoritmo razoavelmente complexo para tentar fazer apenas em SQL, provavelmente fique mais simples em PL/SQL, mas depende desse tipo de definição para entender ao certo como tratar.
RodrigoValentim
Moderador
Moderador
Mensagens: 367
Registrado em: Ter, 25 Mar 2008 3:41 pm
Localização: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Grimaldo,

Tentaria deixar o mais detalhado possível, pois, existe a possibilidade de pedirem um relatório diário de indisponibilidade

Selecionar tudo

[size=85]
SQL> create table t (ds_item varchar2(2000), dti date, dtf date); 
Table created 
SQL> insert into t (ds_item, dti, dtf) values ('EMAIL', ((sysdate - 1) - 4/24), ((sysdate - 1) - 2/24)); 
1 row inserted 
SQL> insert into t (ds_item, dti, dtf) values ('HTTP', ((sysdate - 1) - 8/24), ((sysdate - 1) - 4/24)); 
1 row inserted 
SQL> insert into t (ds_item, dti, dtf) values ('FTP', ((sysdate - 1) - 2/24), ((sysdate - 1) - 1/24)); 
1 row inserted 
SQL> insert into t (ds_item, dti, dtf) values ('PROBLEMA', ((sysdate - 2) - 2/24), ((sysdate - 1) - 1/24)); 
1 row inserted
 SQL> commit;
 Commit complete
 SQL> select * from t;
DS_ITEM                      DTI              DTF
--------------------------- ---------------- ----------------
EMAIL                          27/06/2012 07:51 27/06/2012 09:51
HTTP                           27/06/2012 03:52 27/06/2012 07:52
FTP                             27/06/2012 09:52 27/06/2012 10:52
PROBLEMA                   26/06/2012 09:54 27/06/2012 10:54

SQL>SELECT ds_item,
       to_char(dti, 'dd/mm/yyyy hh24:mi') dti,
       to_char(dtf, 'dd/mm/yyyy hh24:mi') dtf,
       (dtf - dti) * 24
  FROM t;

DS_ITEM                      DTI              DTF                (DTF-DTI)*24
-------------------------- ---------------- ---------------- ------------
EMAIL                         27/06/2012 07:51 27/06/2012 09:51       2
HTTP                          27/06/2012 03:52 27/06/2012 07:52       4
FTP                            27/06/2012 09:52 27/06/2012 10:52       1
PROBLEMA                  26/06/2012 09:54 27/06/2012 10:54       25
 [/size]
grimaldo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qua, 27 Jun 2012 1:55 pm

Caros, vamos considerar que a carga não deve ultrapassar meia-noite, então mesmo que continue após este horário.

A janela começa meia-noite e vai até 23:59:59, 01 dia será considerado assim. dentro desta janela ocorre as paradas e portanto devemos tentar contabilizar o tempo máximo de parada.

o Relatório deve apresentar o formato.

Selecionar tudo

  DIa        Assunto                 parada total  em segundos

  10/05    Sistema Talk              1200 segundos                    
  11/05    Sistema Talk              1000 segundos
lembrando que o Sistema Talk abrage os serviços

Selecionar tudo

Servico    Data Inicio           Data Fim
E-mail     10/05/2012 10:30:20   10/05/2012 11:40:20
Portal     10/05/2012 10:40:30   10/05/2012 13:40:50
Rede       10/05/2012 13:00:00   10/05/2012 16:20:10
Então o tempo total é calculado no horário da primeira parada 10:30:20 (E-mail) até a última parada Rede(16:20:10), mas não é linear, pois existem momentos de disponibilidade dos 03 serviçoes, então não dá para fazer uma simples subtração.

Rodrigo da forma que você exemplificou nãio está correto.

Caros, obrigado pela ajuda e dicas.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Segue uma possibilidade de solução. Pode conter coisas imprecisas, já que assumi algumas premissas onde eu tinha dúvidas.
Há comentários no código para facilitar um pouco a compreensão, não tive tempo de testar melhor se está 100% e qual seria a escalabilidade desse SQL, mas deve estar mais ou menos.

Selecionar tudo

-- início dos dados de exemplo
with t (Servico, Data_Inicio, Data_Fim) as (
-- dia 10
select 'E-mail', to_date('10/05/2012 10:30:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('10/05/2012 11:40:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'Portal', to_date('10/05/2012 10:40:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('10/05/2012 13:40:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'ERP', to_date('10/05/2012 01:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('10/05/2012 18:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'Rede', to_date('10/05/2012 13:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('10/05/2012 17:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
-- dia 11
select 'E-mail', to_date('11/05/2012 10:30:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('11/05/2012 11:40:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'Portal', to_date('11/05/2012 10:40:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('11/05/2012 13:40:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'ERP', to_date('11/05/2012 12:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('11/05/2012 17:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'Rede', to_date('11/05/2012 13:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('11/05/2012 16:20:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
-- dia 15 -- começa indisponibilidade no dia 15 e acaba no dia 16... lógica trata como se fosse tudo no dia 15
select 'Rede', to_date('15/05/2012 08:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('15/05/2012 10:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'ERP', to_date('15/05/2012 09:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('15/05/2012 11:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'E-mail', to_date('15/05/2012 14:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('16/05/2012 20:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
-- dia 12
select 'E-mail', to_date('12/05/2012 09:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('12/05/2012 11:30:00', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 'Portal', to_date('12/05/2012 12:00:00', 'DD/MM/YYYY HH24:MI:SS'), to_date('12/05/2012 13:00:00', 'DD/MM/YYYY HH24:MI:SS') from dual
) -- fim dos dados de exemplo
-- início da lógica
select to_date(data_char, 'YYYYMMDD') as data_indisponib,
       sum(distinct trunc(((dt_hr_fim) - (dt_hr_inic)) * 24, 2)) hr_indisponib
  from (select servico,
               data_inicio,
               data_fim,
               prox_dt_ini,
               prox_dt_fim,
               ant_dt_ini,
               ant_dt_fim,
               contiguo,
               -- coluna "contiguo" determina o particionamento dentro de intervalos de tempo que se sobrepõem
               first_value(data_inicio) over (partition by data_char, nvl(contiguo, rownum) order by data_inicio rows between unbounded preceding and unbounded following) dt_hr_inic,
               last_value(data_fim) over (partition by data_char, nvl(contiguo, rownum) order by data_fim rows between unbounded preceding and unbounded following) dt_hr_fim,
               data_char
        from (select servico,
                     data_inicio,
                     data_fim,
                     lead(data_inicio) over (partition by data_char order by data_inicio, data_fim) prox_dt_ini,
                     lead(data_fim) over (partition by data_char order by data_inicio, data_fim) prox_dt_fim,
                     lag(data_inicio) over (partition by data_char order by data_inicio, data_fim) ant_dt_ini,
                     lag(data_fim) over (partition by data_char order by data_inicio, data_fim) ant_dt_fim,
                     -- quando fim anterior "invadir" inicio posterior ou o início posterior for menor que o fim da linha presente, há sobreposição de tempos
                     coalesce(case when lag(data_fim) over (partition by data_char order by data_inicio, data_fim) > data_inicio then 1 end -- sobrepoe tempo anterior
                             ,case when lead(data_inicio) over (partition by data_char order by data_inicio, data_fim) < data_fim then 1 end -- sobrepoe proximo tempo
                             ) contiguo,
                     data_char
                from (select servico,
                             data_inicio,
                             data_fim,
                             lead(data_inicio) over (partition by to_char(data_inicio, 'YYYYMMDD') order by data_inicio, data_fim) prox_dt_ini,
                             lead(data_fim) over (partition by to_char(data_inicio, 'YYYYMMDD') order by data_inicio, data_fim) prox_dt_fim,
                             lag(data_inicio) over (partition by to_char(data_inicio, 'YYYYMMDD') order by data_inicio, data_fim) ant_dt_ini,
                             lag(data_fim) over (partition by to_char(data_inicio, 'YYYYMMDD') order by data_inicio, data_fim) ant_dt_fim,
                             to_char(data_inicio, 'YYYYMMDD') data_char
                        from t
                     )
               where case when ant_dt_ini < data_inicio and ant_dt_fim > data_fim then 1 end is null
             )
       )
 group by data_char;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

E a resposta obtida para a query:

Selecionar tudo

DATA_INDISPONIB HR_INDISPONIB
--------------- -------------
15/5/2012                  33
10/5/2012                  17
11/5/2012                 6,5
12/5/2012                 3,5
grimaldo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qua, 27 Jun 2012 1:55 pm

Caro Sitja, executei o código e ao testar a base de teste que criei o resultado não saiu correto.

Vou colocar a base aqui, para que você execute.

O valor correto é de 30 minutos e executando sua consulta foi gerado um valor de 48 minutos.

Acredito que o erro está na consideração dos tempos de disponibilidade, que você está levando como linear.

Ex. rede 00:04:00 00:08:00 - tempo indisponivel: 4 minutos
email 00:11:00 00:16:00 - tempo indisponivel : 5 minutos

Total: 9 minutos

Acredito que seu codigo está levando em consideração os tempos: 00:04:00 e 00:16:00 então exibindo 12 minutos.


Sitfja, muito obrigado e veja o que pode me ajudar.

Criei uma tabela com apenas 1 serviço pra facilitar, ok!

Segue inserts

Selecionar tudo

Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:04:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:08:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:11:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:16:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:13:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:14:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:14:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:19:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:21:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:24:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:23:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:26:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:23:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:28:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:31:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:32:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:36:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:34:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:36:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:41:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:49:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:52:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:50:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INDISPON
   (ID, DATEBEGIN, DATEEND)
 Values
   (1, TO_DATE('01/01/2012 00:51:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:52:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Fiz um teste com os dados que você passou, deu 49 minutos, era isso o esperado?

Estava com um probleminha de arredondamento na hora, então coloquei a fórmula em minutos, o que deve melhorar esse ponto:

Selecionar tudo

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as fsitja
 
SQL> 
SQL> create table indispon (id number(10) primary key,
  2                         datebegin date,
  3                         dateend date);
 
Table created
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (1, TO_DATE('01/01/2012 00:04:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:08:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (2, TO_DATE('01/01/2012 00:11:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:16:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (3, TO_DATE('01/01/2012 00:13:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:14:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (4, TO_DATE('01/01/2012 00:14:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:19:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (5, TO_DATE('01/01/2012 00:21:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:24:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (6, TO_DATE('01/01/2012 00:23:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:26:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (7, TO_DATE('01/01/2012 00:23:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:28:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (8, TO_DATE('01/01/2012 00:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:31:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (9, TO_DATE('01/01/2012 00:32:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:36:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (10, TO_DATE('01/01/2012 00:34:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:36:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (11, TO_DATE('01/01/2012 00:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:41:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (12, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:49:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (13, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:52:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (14, TO_DATE('01/01/2012 00:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:50:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> Insert into INDISPON
  2  (ID, DATEBEGIN, DATEEND)
  3  Values
  4  (15, TO_DATE('01/01/2012 00:51:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2012 00:52:00', 'MM/DD/YYYY HH24:MI:SS'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> select to_date(data_char, 'YYYYMMDD') as data_indisponib,
  2         sum(distinct round(((dt_hr_fim) - (dt_hr_inic)) * 24 * 60, 2)) min_indisponib
  3    from (select --servico,
  4                 datebegin,
  5                 dateend,
  6                 prox_dt_ini,
  7                 prox_dt_fim,
  8                 ant_dt_ini,
  9                 ant_dt_fim,
 10                 contiguo,
 11                 -- coluna "contiguo" determina o particionamento dentro de intervalos de tempo que se sobrepõem
 12                 first_value(datebegin) over (partition by data_char, nvl(contiguo, rownum) order by datebegin rows between unbounded preceding and unbounded following) dt_hr_inic,
 13                 last_value(dateend) over (partition by data_char, nvl(contiguo, rownum) order by dateend rows between unbounded preceding and unbounded following) dt_hr_fim,
 14                 data_char
 15          from (select --servico,
 16                       datebegin,
 17                       dateend,
 18                       lead(datebegin) over (partition by data_char order by datebegin, dateend) prox_dt_ini,
 19                       lead(dateend) over (partition by data_char order by datebegin, dateend) prox_dt_fim,
 20                       lag(datebegin) over (partition by data_char order by datebegin, dateend) ant_dt_ini,
 21                       lag(dateend) over (partition by data_char order by datebegin, dateend) ant_dt_fim,
 22                       -- quando fim anterior "invadir" inicio posterior ou o início posterior for menor que o fim da linha presente, há sobreposição de tempos
 23                       coalesce(case when lag(dateend) over (partition by data_char order by datebegin, dateend) > datebegin then 1 end -- sobrepoe tempo anterior
 24                               ,case when lead(datebegin) over (partition by data_char order by datebegin, dateend) < dateend then 1 end -- sobrepoe proximo tempo
 25                               ) contiguo,
 26                       data_char
 27                  from (select --servico,
 28                               datebegin,
 29                               dateend,
 30                               lead(datebegin) over (partition by to_char(datebegin, 'YYYYMMDD') order by datebegin, dateend) prox_dt_ini,
 31                               lead(dateend) over (partition by to_char(datebegin, 'YYYYMMDD') order by datebegin, dateend) prox_dt_fim,
 32                               lag(datebegin) over (partition by to_char(datebegin, 'YYYYMMDD') order by datebegin, dateend) ant_dt_ini,
 33                               lag(dateend) over (partition by to_char(datebegin, 'YYYYMMDD') order by datebegin, dateend) ant_dt_fim,
 34                               to_char(datebegin, 'YYYYMMDD') data_char
 35                          from indispon
 36                       )
 37                 where case when ant_dt_ini < datebegin and ant_dt_fim > dateend then 1 end is null
 38               )
 39         )
 40   group by data_char;
 
DATA_INDISPONIB MIN_INDISPONIB
--------------- --------------
1/1/2012                    49
 
SQL> 
grimaldo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qua, 27 Jun 2012 1:55 pm

Fsitja o tempo é de 30 minutos, veja o exemplo que coloquei acima, pois o tempo deve ser o de indisponibilidade apenas, note no exemplo que existem trechos de disponibilidade e que não devem ser colocados no cálculo.

Fsitja, agradeço muito sua ajuda, veja o exemplo que coloquei antes, obrigado
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante