For 11.2:
1:
with intervals as (
select (level-1) * interval'&minutes'minute as I
from dual
connect by (level-1) * interval'&minutes'minute < interval '1' day
)
select
l.*
,case
when hh_time <= out_time and hh_time+interval'&minutes'minute > in_time
then
&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
else 0 end minutes
from (
select d.*, trunc(in_time,'hh') + i as hh_time
from labor_data d, intervals i
where trunc(in_time,'hh') + i <= out_time
and trunc(in_time,'hh') + i +interval'&minutes'minute > in_time
) l
order by emp_id, hh_time
2:
with
intervals as (
select (level-1) * interval'&minutes'minute as I
from dual
connect by (level-1) * interval'&minutes'minute < interval '1' day
)
,dates as (
select emp_id,trunc(min_time)+intervals.I as hh_time
from
(
select emp_id,min(in_time) min_time, max(out_time) max_time
from labor_data
group by emp_id
) d
,intervals
where trunc(min_time)+intervals.I between min_time and 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
On Tue, Sep 12, 2017 at 12:59 AM, Jeff Chirco <backseatdba@xxxxxxxxx> wrote:
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;
