Re: sql help - generate HH slots based on timeslices

Awesome thank you so much! I am getting a missing right parenthesis error on the second one but the first works perfectly.

On Mon, Sep 11, 2017 at 3:14 PM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

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