Solved

Fractional business days calculation using existing function...

Posted on 2008-10-20
6
825 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:cyimxtck
  • 4
  • 2
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 22758018
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

0
 

Author Comment

by:cyimxtck
ID: 22758894
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22759735
how about something like this?
FUNCTION f_selbusdaysglobaldec(i_startdt DATE, i_enddt DATE)
    RETURN FLOAT
IS
    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;
    start_is_business   PLS_INTEGER := 0;
    end_is_business     PLS_INTEGER := 0;
    return_value        FLOAT;
BEGIN
    IF i_startdt <= i_enddt
    THEN
        first_dt   := i_startdt;
        last_dt    := i_enddt;
    ELSE
        first_dt      := i_enddt;
        last_dt       := i_startdt;
        orientation   := -1;
    END IF;
 
    -- Count the holidays or weekends of the first and end day, if 0 then it must be a work day,
    -- if not 0 then it must be a holiday or weekend, so not a work day
    SELECT   DECODE(COUNT( * ), 0, 1, 1)
      INTO   start_is_business
      FROM   t_globalholiday
     WHERE   holiday = TRUNC(first_dt) OR TO_CHAR(first_dt, 'Dy') NOT IN ('Sat', 'Sun');
 
    SELECT   DECODE(COUNT( * ), 0, 1, 1)
      INTO   end_is_business
      FROM   t_globalholiday
     WHERE   holiday = TRUNC(last_dt) OR TO_CHAR(last_dt, 'Dy') NOT IN ('Sat', 'Sun');
 
    -- Simplify the math, are we looking at a single day or not?
    IF TRUNC(first_dt) != TRUNC(last_dt)
    THEN
        -- Are we looking at 2 or more full days?
        -- If so, then determine number of business days in the range
        -- Not counting the first and last day of the range (as they may be partial days)
        IF TRUNC(last_dt) - TRUNC(first_dt) > 1
        THEN
            SELECT   COUNT( * )
              INTO   return_value
              FROM   (    SELECT   TRUNC(first_dt) + LEVEL d
                            FROM   DUAL
                      CONNECT BY   TRUNC(first_dt) + LEVEL < TRUNC(last_dt))
             WHERE   TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun')
                 AND d NOT IN (SELECT   holiday
                                 FROM   t_globalholiday
                                WHERE   holiday BETWEEN TRUNC(first_dt + 1) AND TRUNC(last_dt - 1));
        ELSE
            return_value   := 0;
        END IF;
 
 
 
        IF start_is_business = 1
        THEN
            return_value   := return_value + (TRUNC(first_dt + 1) - first_dt);
        END IF;
 
        IF end_is_business = 1
        THEN
            return_value   := return_value + (last_dt - TRUNC(last_dt));
        END IF;
    ELSIF end_is_business = 1
    THEN
        -- if we are looking at a single day and that day is a business day,
        -- then simply subtract the two dates to get the difference
        return_value   := (last_dt - first_dt) * orientation;
    ELSE
        -- we are looking at a single non-business day then any hours
        -- are non-business hours so return 0
        return_value   := 0;
    END IF;
 
 
    RETURN return_value;
END f_selbusdaysglobaldec;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:cyimxtck
ID: 31507812
Perfect!  Thanks so much!

B
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22760254
glad I could help
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22760571
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

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Encryption Decryption in Oracle 12 117
Oracle DATE Column Space 11 75
join 2 views with 5 conditions 3 54
Wrong number of values in the INTO list of a FETCH statement 16 60
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question