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

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;

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

FUNCTION f_selbusdaysglobaldec(i_startdt DATE, i_enddt DATE) RETURN FLOATIS 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;

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.