with s as( -- отмеченное фактическое время работы
select to_timestamp('16.03.2011 11:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_1, to_timestamp('16:03:2011 18:20:00', 'dd.mm.yyyy hh24:mi:ss') dt_2 from dual union all
select to_timestamp('17.03.2011 09:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_1, to_timestamp('17:03:2011 21:20:00', 'dd.mm.yyyy hh24:mi:ss') dt_2 from dual union all
select to_timestamp('18.03.2011 20:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_1, to_timestamp('21:03:2011 10:20:00', 'dd.mm.yyyy hh24:mi:ss') dt_2 from dual union all
select to_timestamp('22.03.2011 16:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_1, to_timestamp('23:03:2011 14:30:00', 'dd.mm.yyyy hh24:mi:ss') dt_2 from dual
),
d as ( -- норма часов
select to_timestamp('16.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('16.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual union all
select to_timestamp('17.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('17.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual union all
select to_timestamp('18.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('18.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual union all
select to_timestamp('21.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('21.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual union all
select to_timestamp('22.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('22.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual union all
select to_timestamp('23.03.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_beg, to_timestamp('23.03.2011 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dt_end from dual
)
select trunc(dt_end) day, least(dt_end, dt_2) - greatest(dt_beg, dt_1) work_time
from s, d
where dt_1 between dt_beg and dt_end or dt_beg between dt_1 and dt_2
order by 1;
DAY WORK_TIME
--------------- ------------------------------
16.03.2011 +000000000 07:20:00.000000000
17.03.2011 +000000000 09:00:00.000000000
21.03.2011 +000000000 00:20:00.000000000
22.03.2011 +000000000 03:00:00.000000000
23.03.2011 +000000000 04:30:00.000000000
SQL> |