Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Become a Premium Member and unlock a new, free course in leading technologies each month.
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;
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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;
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;
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');
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.