Re: sql help - generate HH slots based on timeslices

Sorry version is 11.2.0.4

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

> Hi Jeff,
>
> You haven't specified oracle version.
> That's my solution for 12c - you can use it even for other intervals:
> select
> d.*,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 labor_data d
> ,lateral(
> select trunc(in_time,'hh') + (level-1) * interval'&minutes'minute
> hh_time
> from dual
> connect by trunc(in_time,'hh') + (level-1) *
> interval'&minutes'minute<=out_time
> ) l;
>
>
> On Mon, Sep 11, 2017 at 11:37 PM, Jeff Chirco <backseatdba_at_gmail.com>
> wrote:
>
>> Hi all I am need of some SQL help. Maybe because it is a Monday or my
>> brain is just slow today. I am having trouble figuring out how to do this.
>> I have IN and OUT time slices for an employee and I need to break that into
>> half hour slices and the number of minutes worked in that half, relating to
>> the business date or labor date.
>> So for example say I had a table like below:
>>
>> create table labor_data (
>> emp_id number,
>> labor_date date,
>> in_time date,
>> out_time date);
>>
>> insert into labor_data (EMP_ID, LABOR_DATE, IN_TIME, OUT_TIME)
>> values (10000, to_date('01-09-2017', 'dd-mm-yyyy'), to_date('01-09-2017
>> 16:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-09-2017 21:28:00',
>> 'dd-mm-yyyy hh24:mi:ss'));
>>
>> insert into labor_data (EMP_ID, LABOR_DATE, IN_TIME, OUT_TIME)
>> values (10000, to_date('01-09-2017', 'dd-mm-yyyy'), to_date('01-09-2017
>> 22:04:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-09-2017 02:25:00',
>> 'dd-mm-yyyy hh24:mi:ss'));
>> commit;
>>
>> I would like to generate something like below. I am flexible in how the
>> half hours slots look like.
>>
>>
>> Thanks,
>>
>> Jeff
>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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