CODEWITHSUNDEEP
sqloracle-databasedate
Scott
Scott

Reputation: 887

Calculate Business Hours Between Two Dates without Creating Function or View

I realize that this might be a somewhat redundant question BUT I have struggled to follow some of the examples that I did find and I thought I would ask again providing details on my specific scenario.

Here is why I am working with:

I'm able to figure out to calculate hours but I don't know how to get in the business day component.

Upvotes: 1

Views: 484

Answers (1)

Sentinel
Sentinel

Reputation: 6449

Starting with your example of 8AM Friday through 9AM Monday:

with dates as (
 Select timestamp '2019-05-31 08:00:00' start_date
      , timestamp '2019-06-03 09:00:00' end_date
   from dual
)

We need to generate the days in between. We can do that with a recursive query:

, recur(start_date, calc_date, end_date) as (
  -- Anchor Part
  select start_date
       , trunc(start_date)
       , end_date
    from dates

  -- Recrusive Part
  union all
  select start_date
       , calc_date+1
       , end_date
    from recur
   where calc_date+1 < end_Date
)

From that we need to figure out a few things like, is the calc_day a weekday or a weekend, and what are the starting and ending times for the calc_day, we can then take those values and use a little date arithmetic to find the number of hours worked on that day (returned as day to second interval since we started with timestamps):

, days as (
  select calc_date
       , case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay
       , greatest(start_date, calc_date + interval '8' hour) start_time
       , least(end_date, calc_date      + interval '16:30' hour to minute) end_time

       , least( ( least(end_date, calc_date      + interval '16:30' hour to minute) 
                - greatest(start_date, calc_date + interval '8' hour)
                ) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
              , interval '8' hour
         ) daily_hrs
    from recur
   where start_date < (calc_date + interval '16:30' hour to minute)
     and (calc_date + interval '8' hour) < end_date
)

Note that in the above step, we've limited the daily hours to 8 hours a day, and the where clause guards against start or end days that are outside business hours. The final step is to sum the hours. Unfortunately Oracle doesn't have any native interval aggregate or analytic functions, but we can still manage by converting the intervals to seconds, summing them and then converting them back to an interval for output:

select calc_date
     , daily_hrs
     , numtodsinterval(sum( extract(hour   from daily_hrs)*60*60
                          + extract(minute from daily_hrs)*60
                          + extract(second from daily_hrs)
                          ) over (order by calc_date)
                      ,'second') run_sum
  from days;

I've done the sum above as an analytic function so we can see some of the intervening data, but if you just want the final output you can change the last part of the query to this:

select numtodsinterval(sum( extract(hour from daily_hrs)*60*60
                          + extract(minute from daily_hrs)*60
                          + extract(second from daily_hrs)
                          )
                      ,'second') run_sum

Here's a db<>fiddle with the whole query in action. Note that in the fiddle, I've altered the DB session's NLS_TERRITORY setting to AMERICA to make the query work since the first day of the week is country specific. The second query in the fiddle replaces the territory specific function:

case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end

with a location and language agnostic calculation:

case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end

Upvotes: 2

Related Questions