Home -> Mailing Lists -> Oracle-L -> Re: sql help - generate HH slots based on timeslices

Re: sql help - generate HH slots based on timeslices

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 12 Sep 2017 00:29:14 +0300
Message-ID: <CAOVevU43M+4h=ah25RSCK1NoYX6RL=F=W67JCUGiMt9bXG9bjw_at_mail.gmail.com> 








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


Received on Mon Sep 11 2017 - 23:29:14 CEST












Original text of this message