Thanks Sayan! Those queries work in 12c but since I am currently in 11g I
am trying to figure out how to convert it. I have not seen LATERAL yet.
On Mon, Sep 11, 2017 at 2:29 PM, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Another variant (if you want to use generated calendar):
with dates as (
select emp_id,hh_time
from
(
select emp_id,min(in_time) min_time, max(out_time) max_time -- you
can choose start/end date here
from labor_data
group by emp_id
) d
,lateral(
select trunc(min_time,'hh') + (level-1) * interval'&minutes'minute
hh_time
from dual
connect by trunc(min_time,'hh') + (level-1) *
interval'&minutes'minute<=max_time
)
)
select
emp_id
,hh_time
,nvl(
(select
&minutes
+(hh_time-greatest(hh_time,in_time))*24*60
+(least(out_time,(hh_time+interval'&minutes'minute)) -
(hh_time+interval'&minutes'minute) )*24*60
as minutes
from labor_data l
where l.emp_id=dates.emp_id
and hh_time <= out_time
and hh_time+interval'&minutes'minute > in_time
),0) as minutes
from
dates;
