Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Fractional business days calculation using existing function...

Posted on 2008-10-20
Medium Priority
833 Views
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?

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;
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
count_first := 0;

ELSIF first_dowk = 1

THEN
count_first := 0;

ELSE

NULL;

END IF;

IF last_dowk = 7

THEN

END IF;

return_value := ( ( wks_btwn * 5 ) +
( last_dowk - first_dowk + count_first ) +
) * orientation;

RETURN return_value;

END F_SELBusdaysGlobalDec;
``````
0
Question by:cyimxtck
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 74

Expert Comment

ID: 22758018
I had to solve a similar problem...

try using this...

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
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;

THEN
v_count    := v_count + GREATEST(0, p_end_hour - GREATEST(v_start_time, p_start_hour));
END IF;

THEN
v_count    := v_count + GREATEST(0, LEAST(v_end_time, p_end_hour) - p_start_hour);
END IF;
ELSE
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;
``````
0

Author Comment

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".

Thanks,

B
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 22759735
``````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;
count_hdays         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)
FROM   t_globalholiday
WHERE   holiday = TRUNC(first_dt) OR TO_CHAR(first_dt, 'Dy') NOT IN ('Sat', 'Sun');

SELECT   DECODE(COUNT( * ), 0, 1, 1)
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;

THEN
return_value   := return_value + (TRUNC(first_dt + 1) - first_dt);
END IF;

THEN
return_value   := return_value + (last_dt - TRUNC(last_dt));
END IF;
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;
``````
0

Author Closing Comment

ID: 31507812
Perfect!  Thanks so much!

B
0

LVL 74

Expert Comment

ID: 22760254
0

LVL 74

Expert Comment

ID: 22760571
I found a bug in my code above....

change the "xxxx_is_business" queries like this...
``````  SELECT   DECODE(COUNT( * ), 0, 1, 0)
FROM   t_globalholiday
WHERE   holiday = TRUNC(first_dt) OR TO_CHAR(first_dt, 'Dy')  IN ('Sat', 'Sun');

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

## Featured Post

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For manyā¦
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month8 days, 20 hours left to enroll