SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Business hours calculation - weekends already excluded and how to exclude holidays ?

soufirJan 19 2016 — edited Jan 22 2016

CREATE OR REPLACE FUNCTION	working_hours
(	in_start_dt	IN	DATE	DEFAULT SYSDATE
,	in_end_dt	IN	DATE	DEFAULT	SYSDATE

)

RETURN

NUMBER

DETERMINISTIC

--	*************************************
--	**	**
--	b u s i n e s s _ h o u r s
--	**	**
--	*************************************

--	working_hours returns the number of work houts (8 am through 8 pm,
--	Monday through Friday) between in_start_dt and in_end_dt.
--	If in_start_dt > in_end_dt, the results will be <= 0.
--	Holidays are not considered.

	d	NUMBER;	-- Hours of either start_dt or end_dt after midnight
	end_dt	DATE	:= GREATEST (in_start_dt, in_end_dt); -- In case dates were in wrong order
	return_val	NUMBER;	-- Total number of working hours
	start_dt	DATE	:= LEAST	(in_start_dt, in_end_dt); -- In case dates were in wrong order

holiday_cnt NUMBER;

BEGIN

WITH	all_days	AS
(
SELECT	start_dt + LEVEL - 1	AS a_dt
FROM	dual
CONNECT BY LEVEL <= 1 + TRUNC (end_dt) - TRUNC (start_dt)
MINUS
SELECT trunc(calendar_day)
FROM	public_holiday
)
SELECT	SUM (12)
INTO	return_val
FROM	all_days
WHERE	TO_CHAR ( a_dt
, 'Dy'
, 'NLS_DATE_LANGUAGE = ''ENGLISH'''
)	NOT IN ('Fri', 'Sat');
-- Adjust hours from start_dt, if necessary
IF TO_CHAR ( start_dt
, 'Dy'
, 'NLS_DATE_LANGUAGE = ''ENGLISH'''
) NOT IN ('Fri', 'Sat')

THEN

SELECT COUNT (*)
INTO	holiday_cnt
FROM	public_holiday
WHERE	TRUNC(calendar_day) between TRUNC(start_dt) and TRUNC(end_dt);
	IF holiday_cnt > 0
	THEN
		d := 24 * (start_dt - TRUNC (start_dt));
		IF d >= 20
		THEN	-- Don't count start_dt itself
				return_val := return_val - 12;
		ELSIF d > 8
		THEN	-- Don't count part of start_dt
				return_val := return_val - (d - 8);
		END IF;
	END IF;

END IF;

	-- Adjust hours from end_dt, if necessary
	IF TO_CHAR ( end_dt
	, 'Dy'
	, 'NLS_DATE_LANGUAGE = ''ENGLISH'''
	) NOT IN ('Fri', 'Sat')
	THEN
	d := 24 * (end_dt - TRUNC (end_dt));
	IF d <= 8
	THEN	-- Don't count end_dt itself
	return_val := return_val - 12;
	ELSIF d < 20
	THEN	-- Don't count part of end_dt
	return_val := return_val - (20 - d);
	END IF;
	END IF;

	IF in_start_dt > in_end_dt
	THEN
	return_val := -return_val;
	END IF;

	RETURN	return_val;
END	working_hours

;

This post has been answered by Etbin on Jan 22 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 19 2016

Added on Jan 19 2016

30 comments

3,129 views