Re: sql help - generate HH slots based on timeslices

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_at_gmail.com> 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;
>

--
http://www.freelists.org/webpage/oracle-l