Skip to Main Content
  • Questions

  • Need a sql to find break hours hours between two dates and between 10PM to next day 6AM. 30mins break for every 4 hours

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ram.

Asked: June 13, 2024 - 3:12 pm UTC

Last updated: August 15, 2024 - 4:13 pm UTC

Version: R12

Viewed 1000+ times

You Asked

Could you please kindly check and advise on the following. i need to find break hours between two dates. and also i need to find if this break mins is within night hours (10PM to 6AM next day).

ex:
employee work schedule 11-MAY-2012 16:00:00 and 12-MAY-2012 09:00:00.

Break hour that fall between 10PM to 6 AM is as follows

8:00PM to 8:30PM - 1st Break
12:30 Am to 1:00 AM - 2nd Break
5:00AM to 05:30 AM - 3rd break

two of the breaks are between 10PM and 6AM next day hence my night hours break time is 60mins.

i am using following, but is there any better solution

 (SELECT COUNT(1)
       FROM (SELECT TO_CHAR ((:SHIFT_START + ((val*4.5)/24)), 'YYYY-MM-DD HH24:MI:SS') TIME ,VAL
            FROM (select to_number(val,0) VAL
    from (select distinct regexp_substr('1,2,3,4,5','[^,]+',1,level) val 
     from dual
     connect by level <=regexp_count('1,2,3,4,5',',')+1
     order by val)
    WHERE ROWNUM <=
      (  FLOOR (
         (  24
          * (  TO_DATE (
          TO_CHAR (:SHIFT_END, 'YYYY-MM-DD hh24:mi'),
          'YYYY-MM-DD hh24:mi')
          - TO_DATE (
          TO_CHAR (:SHIFT_START,
             'YYYY-MM-DD hh24:mi'),
          'YYYY-MM-DD hh24:mi')))
       / 4))
     )
    ) DT
      WHERE TO_DATE (DT.TIME,'YYYY-MM-DD HH24:MI:SS')
        BETWEEN TO_DATE(TRUNC(:SHIFT_START),'YYYY-MM-DD HH24:MI:SS')+22/24 AND 
   TO_DATE(TRUNC(:SHIFT_START),'YYYY-MM-DD HH24:MI:SS')+24/24+6/24 
   )*0.5
  ELSE 0 END   NIGHT_BREAK_HOURS


Appreciated your kind help in check and advise on this

and Chris said...

Here's one way to approach it:

- Select the break/start end times and whether they fall in the night shift
- Get a row for each calendar day (excluding time) the shift covers
- Cross join these to generate all the possible break times between the start and end days
- Join this list of all possible breaks to those that fall within the start/end times of the shift

Which looks something like this:

with shift_dates as (
  select to_date ( '11-MAY-2012 16:00:00', 'DD-MON-YYYY HH24:MI:SS' ) st_dt,
         to_date ( '12-MAY-2012 09:00:00', 'DD-MON-YYYY HH24:MI:SS' ) en_dt
  from   dual
), break_times as (
  select 0.5 st_time, 1 en_time, 'night' night from dual union all
  select 5   st_time, 5.5 en_time, 'night' night from dual union all
  select 20  st_time, 20.5 en_time, 'day' night from dual
), shift_breaks as ( 
  select distinct 
    trunc ( dt ) + ( st_time / 24 ) break_start,
    trunc ( dt ) + ( en_time / 24 ) break_end,
    night
  from   shift_dates unpivot ( dt for col in ( st_dt, en_dt ) )
  cross  join break_times
)
select sb.* 
from   shift_dates
join   shift_breaks sb
on     st_dt < break_start
and    en_dt > break_end
order  by break_start

BREAK_START          BREAK_END            NIGHT
-------------------- -------------------- -----
11-MAY-2012 20:00:00 11-MAY-2012 20:30:00 day  
12-MAY-2012 00:30:00 12-MAY-2012 01:00:00 night
12-MAY-2012 05:00:00 12-MAY-2012 05:30:00 night


I'm sure others will find different ways to address this.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ram, August 02, 2024 - 9:28 am UTC

Hi Chris, These dates are not static and will always change dynamically for each employee. the solution i had written is work for most of the scenarios but if the break is less than 30 mins in night hours it is failing. ex: '20-JUL-2024 13:15:00' and '20-JUL-2024 22:15:00', break hours are 5:15 - 5:45 and 9:45 - 10:15, here night hours are 15 mins and as per the formula i had written, i am getting 30mins as break mins which is resulting total night hours in to negative (night hours-break mins). Could you please kindly help to check and advise if there is any better solution. the criteria is every 4 hours 30mins is break and all the break mins between 10PM and 6AM should be deducted from total night hours for night hours payment calculation. Appreciated all your kind help
Chris Saxon
August 06, 2024 - 12:37 pm UTC

What exactly is your query that's giving the incorrect result?

If different employees have different break times, do you have these stored in a table somewhere? If not, you should. Then it's a matter of switching the break_times generated table in the query above for the actual break times table and making any necessary adjustments.

My understanding of the requirement

Stew Ashton, August 13, 2024 - 3:15 pm UTC

The subject matter is night hours payment calculation.

- Employees are paid extra for hours actually worked from 10 P.M. to 6 A.M.
-- shift start time can vary
-- shift end time can vary
-- from shift start, employee works 4 hours then breaks for 30 minutes
then works 4 hours then breaks for 30 minutes
and so on until shift end.
-- "night hours" are hours within the 4-hour work periods that fall between 10 P.M. and 6 A.M.

The OP is trying to calculate total night hours (including breaks) minus breaks.
I suggest directly calculating the night hours within each 4-hour work period.

- Approach:
-- calculate work periods when actual work was done
-- calculate night work duration within each work period
--- if period start at night then night work start = period start else = next night work start
--- if period end at night then night work end = period end else = previous night work end
--- sum night work durations when night work end is later than night work start.

In my code, the first WITH clause generates 48 test cases, with shifts starting every 30 minutes and lasting 17 hours.
alter session set nls_date_format='mm-dd hh24:mi';

with shifts(shift_start, shift_end) as (
  select trunc(sysdate) + level/48, 
    trunc(sysdate) + level/48 + 17/24
  from dual
  connect by level <= 48
)
, work_periods as (
  select SHIFT_START, SHIFT_END,
    WORK_START, extract(hour from cast(work_start as timestamp)) start_hour,
    WORK_END, extract(hour from cast(work_end as timestamp)) end_hour
  from shifts, lateral(
    select shift_start + 4.5/24 * (level - 1) WORK_START,
      least(shift_end, shift_start + 4.5/24 * (level - 1) + 4/24) WORK_END
    from dual
    connect by shift_start + 4.5/24 * (level - 1) < shift_end
  )
)
select shift_start, shift_end,
  round(24 * sum(night_end - night_start), 4) night_hours
from (
  select shift_start, shift_end,
    case when start_hour >= 22 or start_hour < 6 then work_start else trunc(work_start) + 22/24 end night_start,
    case when end_hour >= 22 or end_hour < 6 then work_end else trunc(work_end) + 6/24 end night_end
  from work_periods
) a
where night_end > night_start
group by shift_start, shift_end
order by 1,2

shift_start shift_end   night_hours
08-13 00:30 08-13 17:30 5
08-13 01:00 08-13 18:00 4,5
08-13 01:30 08-13 18:30 4
08-13 02:00 08-13 19:00 4
08-13 02:30 08-13 19:30 3,5
08-13 03:00 08-13 20:00 3
08-13 03:30 08-13 20:30 2,5
08-13 04:00 08-13 21:00 2
08-13 04:30 08-13 21:30 1,5
08-13 05:00 08-13 22:00 1
08-13 05:30 08-13 22:30 1
08-13 06:00 08-13 23:00 1
08-13 06:30 08-13 23:30 1,5
08-13 07:00 08-14 00:00 2
08-13 07:30 08-14 00:30 2,5
08-13 08:00 08-14 01:00 3
08-13 08:30 08-14 01:30 3,5
08-13 09:00 08-14 02:00 3,5
08-13 09:30 08-14 02:30 4
08-13 10:00 08-14 03:00 4,5
08-13 10:30 08-14 03:30 5
08-13 11:00 08-14 04:00 5,5
08-13 11:30 08-14 04:30 6
08-13 12:00 08-14 05:00 6,5
08-13 12:30 08-14 05:30 7
08-13 13:00 08-14 06:00 7,5
08-13 13:30 08-14 06:30 7
08-13 14:00 08-14 07:00 7
08-13 14:30 08-14 07:30 7
08-13 15:00 08-14 08:00 7
08-13 15:30 08-14 08:30 7
08-13 16:00 08-14 09:00 7
08-13 16:30 08-14 09:30 7
08-13 17:00 08-14 10:00 7,5
08-13 17:30 08-14 10:30 7,5
08-13 18:00 08-14 11:00 7
08-13 18:30 08-14 11:30 7
08-13 19:00 08-14 12:00 7
08-13 19:30 08-14 12:30 7
08-13 20:00 08-14 13:00 7
08-13 20:30 08-14 13:30 7
08-13 21:00 08-14 14:00 7
08-13 21:30 08-14 14:30 7,5
08-13 22:00 08-14 15:00 7,5
08-13 22:30 08-14 15:30 7
08-13 23:00 08-14 16:00 6,5
08-13 23:30 08-14 16:30 6
08-14 00:00 08-14 17:00 5,5
Best regards,

Stew
Chris Saxon
August 15, 2024 - 4:13 pm UTC

Thanks for sharing Stew

Adjustment

Stew Ashton, August 13, 2024 - 3:30 pm UTC

In my previous comment, no rows are returned if there are no nighttime work hours. If the OP wants to return a row with zero hours, the main SELECT should be as follows:
select shift_start, shift_end,
  round(24 * sum(
    case when night_end > night_start then night_end - night_start else 0 end
  ), 4) night_hours
from (
  select shift_start, shift_end,
    case when start_hour >= 22 or start_hour < 6 then work_start else trunc(work_start) + 22/24 end night_start,
    case when end_hour >= 22 or end_hour < 6 then work_end else trunc(work_end) + 6/24 end night_end
  from work_periods
) a
group by shift_start, shift_end
order by 1,2

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.