Re: sql help - generate HH slots based on timeslices

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)) -

      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

     (
       select emp_id,min(in_time) min_time, max(out_time) max_time
       from labor_data
       group by emp_id
     ) d
     ,intervals

   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

      and hh_time <= out_time
      and hh_time+interval'&minutes'minute > in_time

     dates

On Tue, Sep 12, 2017 at 12:59 AM, Jeff Chirco <backseatdba_at_gmail.com> 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_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;
>>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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