clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 541802 fiddles created (11229 in the last week).

alter session set nls_territory = 'AMERICA';
 hidden batch(es)


with dates as ( Select timestamp '2019-05-31 08:00:00' start_date , timestamp '2019-06-03 09:00:00' end_date from dual ) , recur(start_date, calc_date, end_date) as ( -- Anchor Part select start_date , trunc(start_date) , end_date from dates -- Recrusive Part union all select start_date , calc_date+1 , end_date from recur where calc_date+1 < end_Date ) , days as ( select calc_date , case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay , greatest(start_date, calc_date + interval '8' hour) start_time , least(end_date, calc_date + interval '16:30' hour to minute) end_time , least( ( least(end_date, calc_date + interval '16:30' hour to minute) - greatest(start_date, calc_date + interval '8' hour) ) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end , interval '8' hour ) daily_hrs from recur where start_date < (calc_date + interval '16:30' hour to minute) and (calc_date + interval '8' hour) < end_date ) select calc_date , daily_hrs , numtodsinterval(sum( extract(hour from daily_hrs)*60*60 + extract(minute from daily_hrs)*60 + extract(second from daily_hrs) ) over (order by calc_date) ,'second') run_sum from days;
CALC_DATE DAILY_HRS RUN_SUM
31-MAY-19 +000000000 08:00:00.000000000 +000000000 08:00:00.000000000
01-JUN-19 +000000000 08:00:00.000000000
02-JUN-19 +000000000 08:00:00.000000000
03-JUN-19 +000000000 01:00:00.000000000 +000000000 09:00:00.000000000
 hidden batch(es)


alter session set nls_territory = 'UNITED KINGDOM';
 hidden batch(es)


with dates as ( Select timestamp '2019-05-31 08:00:00' start_date , timestamp '2019-06-03 09:00:00' end_date from dual ) , recur(start_date, calc_date, end_date) as ( -- Anchor Part select start_date , trunc(start_date) , end_date from dates -- Recrusive Part union all select start_date , calc_date+1 , end_date from recur where calc_date+1 < end_Date ) , days as ( select calc_date , case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay_bad , case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end isWeekDay , greatest(start_date, calc_date + interval '8' hour) start_time , least(end_date, calc_date + interval '16:30' hour to minute) end_time , least( ( least(end_date, calc_date + interval '16:30' hour to minute) - greatest(start_date, calc_date + interval '8' hour) ) * case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end , interval '8' hour ) daily_hrs from recur where start_date < (calc_date + interval '16:30' hour to minute) and (calc_date + interval '8' hour) < end_date ) select calc_date , isweekday , isweekday_bad , daily_hrs , numtodsinterval(sum( extract(hour from daily_hrs)*60*60 + extract(minute from daily_hrs)*60 + extract(second from daily_hrs) ) over (order by calc_date) ,'second') run_sum from days;
CALC_DATE ISWEEKDAY ISWEEKDAY_BAD DAILY_HRS RUN_SUM
31-MAY-19 1 1 +000000000 08:00:00.000000000 +000000000 08:00:00.000000000
01-JUN-19 1 +000000000 08:00:00.000000000
02-JUN-19 +000000000 08:00:00.000000000
03-JUN-19 1 +000000000 01:00:00.000000000 +000000000 09:00:00.000000000
 hidden batch(es)