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>
