asked on

Fractional business days calculation using existing function...

I have created a function that calculates business days. This works very well and life is good. Until the business wants me to create another function that will calculate less than whole days. (i.e. 1.2221 days, 23.4534 days, etc.)

My calculation is just for whole days. Changing the datatype to FLOAT doesn't modify the code.

How can I calculate fractional days in here?

PLEASE HELP!! :)

Thanks,

B

FUNCTION F_SELBusdaysGlobalDec
 ( 
   i_startdt    DATE
 , i_enddt      DATE 
 )
 
RETURN FLOAT
 
IS
 
   cln_start_dt DATE        := ( i_startdt );
   cln_end_dt   DATE        := ( i_enddt );
   first_dt     DATE;
   last_dt      DATE;
   orientation  PLS_INTEGER := 1;
   first_dowk   PLS_INTEGER;
   count_first  PLS_INTEGER := 1;
   last_dowk    PLS_INTEGER;
   wks_btwn     PLS_INTEGER;
   sat_adjst    PLS_INTEGER := 0;
   sun_adjst    PLS_INTEGER := 0;
   count_hdays  PLS_INTEGER := 0;
   return_value FLOAT;
 
BEGIN
 
    
 
   IF cln_start_dt <= cln_end_dt
 
   THEN
      first_dt := cln_start_dt;
      last_dt := cln_end_dt;
 
   ELSE
      first_dt := cln_end_dt;
      last_dt := cln_start_dt;
      orientation := -1;
   
   END IF;
   
   SELECT COUNT( * )
     INTO count_hdays
     FROM T_GlobalHoliday
    WHERE holiday BETWEEN first_dt
                      AND last_dt;
   first_dowk := TO_NUMBER( TO_CHAR( first_dt, 'D' ) );
   last_dowk := TO_NUMBER( TO_CHAR( last_dt, 'D' ) );
   wks_btwn := TRUNC ( ( last_dt - first_dt ) / 7 );
   
   IF first_dowk > last_dowk
   
   THEN
      wks_btwn := wks_btwn + 1;
   END IF;
   
   IF first_dowk = 7
   
   THEN
      sat_adjst := 1;
      count_first := 0;
 
   ELSIF first_dowk = 1
   
   THEN
      sat_adjst := 1; 
      count_first := 0;
   
   ELSE
   
      NULL;
   
   END IF;
   
   IF last_dowk = 7
   
   THEN
      sat_adjst := sat_adjst - 1;
   
   END IF;
   
   return_value := ( ( wks_btwn * 5 ) +
                     ( last_dowk - first_dowk + count_first ) +
                     sat_adjst - count_hdays
                   ) * orientation;
                   
   RETURN return_value;
 
END F_SELBusdaysGlobalDec;

Open in new window

Sean Stuber

I had to solve a similar problem...

try using this...

insert your own function for the "business_days_between"
you will also need a "is_business_day" function that returns Y or N.

this is because if you have a start or end day on a weekend you obviously don't want to include those hours. your day counting function will already exlude them, you don't want the hours to put them back in.

the p_start_hour and p_end_hour parameters are used to define the start and end of your business days.

If you start your business day at 6:30am and end at 5:30pm you would pass in

6.5 for p_start_hour and
17.5 for p_end hour

    FUNCTION business_hours_between(
        p_start        IN   DATE,
        p_end          IN   DATE,
        p_start_hour   IN   NUMBER,
        p_end_hour     IN   NUMBER
    )
        RETURN NUMBER
    IS
        v_start        DATE;
        v_end          DATE;
        v_count        NUMBER := 0;
        v_start_time   NUMBER;
        v_end_time     NUMBER;
    BEGIN
        -- If the parameters are passed in chronologically backwards
        -- then reverse them internally and continue.
        IF p_start > p_end
        THEN
            v_start    := p_end;
            v_end      := p_start;
        ELSIF p_start < p_end
        THEN
            v_start    := p_start;
            v_end      := p_end;
        ELSE
            -- start = end so 0 time
            RETURN 0;
        END IF;
 
        IF p_start_hour < 0 OR p_start_hour >= p_end_hour OR p_end_hour > 24
        THEN
            raise_application_error(-20001, 'Invalid business hours');
        END IF;
 
        v_start_time    := (v_start - TRUNC(v_start)) * 24;
        v_end_time      := (v_end - TRUNC(v_end)) * 24;
 
        IF TRUNC(v_start) != TRUNC(v_end)
        THEN
            IF TRUNC(v_end) - TRUNC(v_start) > 1
            THEN
                -- Business days between is end-point INclusive and ignores time
                -- so we only want to count the hours in whole days.
                v_count    := business_days_between(v_start + 1, v_end - 1) *(p_end_hour - p_start_hour);
            END IF;
 
            IF is_business_day(v_start) = 'Y'
            THEN
                v_count    := v_count + GREATEST(0, p_end_hour - GREATEST(v_start_time, p_start_hour));
            END IF;
 
            IF is_business_day(v_end) = 'Y'
            THEN
                v_count    := v_count + GREATEST(0, LEAST(v_end_time, p_end_hour) - p_start_hour);
            END IF;
        ELSE
            IF is_business_day(v_end) = 'Y'
            THEN
                v_count    :=
                             GREATEST(0, LEAST(v_end_time, p_end_hour) - GREATEST(v_start_time, p_start_hour));
            END IF;
        END IF;
 
        RETURN v_count;
    END business_hours_between;

Open in new window

cyimxtck

ASKER

Do you have any idea how to wire this into the existing function?

Several differences would be that we are not looking to supply times for each calculation; we are just using the entire day for full days (i.e. 0:00 hours to 23:59 hours) and have everything stored in a table already for holidays. Folks here have populated a "standard" holiday calendar so we are using that instead of a function to give a "Y" or "N".

Please give me your thoughts.

Thanks,

B

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyimxtck

ASKER

Perfect! Thanks so much!

B

Sean Stuber

glad I could help

Sean Stuber

I found a bug in my code above....

change the "xxxx_is_business" queries like this...

  SELECT   DECODE(COUNT( * ), 0, 1, 0)
      INTO   start_is_business
      FROM   t_globalholiday
     WHERE   holiday = TRUNC(first_dt) OR TO_CHAR(first_dt, 'Dy')  IN ('Sat', 'Sun');
 
    SELECT   DECODE(COUNT( * ), 0, 1, 0)
      INTO   end_is_business
      FROM   t_globalholiday
     WHERE   holiday = TRUNC(last_dt) OR TO_CHAR(last_dt, 'Dy')  IN ('Sat', 'Sun');

Open in new window