Re: sql help - generate HH slots based on timeslices

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

     from dual
     connect by trunc(in_time,'hh') + (level-1) *
interval'&minutes'minute<=out_time
     ) l;

> 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