1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
with t as
(
select to_date('01/01/2009 10:00','dd/mm/yyyy hh24:mi')as datestat,'arret' as stat from dual
union
select to_date('01/01/2009 11:00','dd/mm/yyyy hh24:mi'), 'marche' from dual
union
select to_date('01/01/2009 11:30','dd/mm/yyyy hh24:mi'), 'arret' from dual
union
select to_date('01/01/2009 12:30','dd/mm/yyyy hh24:mi'), 'marche' from dual
union
select to_date('01/01/2009 14:00','dd/mm/yyyy hh24:mi'), 'arret' from dual
union
select to_date('01/01/2009 15:00','dd/mm/yyyy hh24:mi'), 'marche' from dual
)
select sum(date_fin-date_deb)*1440 "CUMUL"
from (
select stat , datestat date_deb
, lead(datestat,1) over( order by datestat) date_fin
from t)
where stat='arret'
and date_fin is not null
/ |
Partager