How to get business days or hours between two date

问题:

How can I calculate business hours or days between two dates in oracle 10g?

For example we have two dates; 14/08/2012 9:30 and 16/08/2012 12:00 And we have working hours 09:30 to 18:30 in weekdays.

How can I calculate working hours or days excluding National Holidays, Sat & Sun with oracle 10g?

回答1:

You can't. It's as simple as that. National holidays vary the world over, they vary year by year and extra ones can be added or taken away at any time. Additionally some jurisdictions carry over national holidays that fall on the weekend and have them the next week; others don't.

You'll need to create a calender table and flag national holidays / weekends etc in this.

For instance

create table calender
  ( day date
  , weekend varchar2(1)
  , holiday varchar2(1)
    );

Then insert some data into it...

 insert into calender (day, weekend)
 select trunc(sysdate + level)
      , case when to_date(sysdate + level,'fmDAY') in ('SATURDAY','SUNDAY') 
                  then 'Y' else 'N' end
   from dual
connect by level <= 365

Lastly, manually update what you count as a national holiday in there.

You can then select working days, depending on how you populated it with something like this:

select count(*)
  from calender
 where day between :startdate and :enddate
   and weekend = 'N'
   and holiday = 'N'

回答2:

Found the solution for calculating Business Hours excluding weekends and holidays list in table.

https://forums.oracle.com/forums/thread.jspa?messageID=9322860

        create or replace
        FUNCTION business_hours(
              in_start_dt IN DATE DEFAULT SYSDATE ,
              in_end_dt   IN DATE DEFAULT SYSDATE )
            RETURN NUMBER DETERMINISTIC
          IS
            --  business_hours returns the number of work hours (9.30 am through 6.30 pm,
            --  Monday through Friday) between in_start_dt and in_end_dt.
            --  If in_start_dt > in_end_dt, the results will be <= 0.
            d          NUMBER;                                    -- Hours of either start_dt or end_dt after midnight
            end_dt     DATE := GREATEST (in_start_dt, in_end_dt); -- In case dates were in wrong order
            return_val NUMBER;                                    -- Total number of working hours
            start_dt   DATE := LEAST (in_start_dt, in_end_dt);    -- In case dates were in wrong order
          BEGIN
          WITH all_days AS
            (SELECT TRUNC(start_dt) + LEVEL - 1 AS a_dt
            FROM dual
              CONNECT BY LEVEL <= 1 + TRUNC (end_dt) - TRUNC (start_dt)
            MINUS
            SELECT hol_dt FROM holiday
            )
          SELECT SUM (9)
          INTO return_val
          FROM all_days
          WHERE TO_CHAR ( a_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun');
          -- Adjust hours from start_dt, if necessary
          IF TO_CHAR ( start_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun') THEN
            d := 24 * (start_dt - TRUNC (start_dt));
            IF d >= 18.5 THEN -- Don't count start_dt itself
              return_val := return_val - 9;
            ELSIF d > 9.5 THEN -- Don't count part of start_dt
              return_val := return_val - (d - 9.5);
            END IF;
          END IF;
          -- Adjust hours from end_dt, if necessary
          IF TO_CHAR ( end_dt , 'Dy' , 'NLS_DATE_LANGUAGE = ''ENGLISH''' ) NOT IN ('Sat', 'Sun') THEN
            d := 24 * (end_dt - TRUNC (end_dt));
            IF d <= 9.5 THEN -- Don't count end_dt itself
              return_val := return_val - 9;
            ELSIF d < 18.5 THEN -- Don't count part of end_dt
              return_val := return_val - (18.5 - d);
            END IF;
          END IF;
          IF in_start_dt > in_end_dt THEN
            return_val  := -return_val;
          END IF;
          RETURN return_val;
        END business_hours ;

回答3:

I just did something like that. Here is an sql code piece to calculate the work done between stime and etime by employees in a table T

create table t ( NAME varchar(50), stime date, etime date, clockin number,clockout number );
insert into t values ( 'JOHN', to_date( '18/12/2003 11:40','dd/mm/yyyy hh24:mi'), to_date( '22/12/2003 14:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'JOHN', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'TOM', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );


with oo as (SELECT LEVEL-1 rn FROM dual CONNECT BY LEVEL <= 365) --JUST A TABLE WITH INTEGER RECORDS FROM 1 To 365 
select
t.NAME ,sum(least( trunc(stime)+18.5/24+rn, etime )-greatest( stime, trunc(stime)+9.5/24+rn))*24 as WorkHours -- Get workhours between 09:30-18:30 
from oo
inner join t on oo.rn < (trunc(etime)-trunc(stime)+1) 
      and to_char(stime+rn,'Dy') not in ( 'Sat', 'Sun' )  --For eliminating Weekends
      and to_char(trunc(stime)+rn,'DD.MM')  != '04.07'   -- For eliminating Holidays
group by t.NAME 

You can delete the group by line and remove the sum function to see how it works by generating rows for each day worked.