Q

Calculating approval date, part 3: Code for a faster day counter

How can I write a function to calculate the approval date which is n days (variable) from a given date, excluding

Thursdays and Fridays?

The following is part 3 of Frank's response. Click for part 2, A faster day counter.

Code for a faster day counter

 

Package Head

 CREATE OR REPLACE PACKAGE pk_approval AS FUNCTION approval_dt ( in_event_dt IN DATE, -- Starting date in_day_cnt IN NUMBER -- Number of "work" days ) RETURN DATE; FUNCTION approval_n ( in_dow_val IN PLS_INTEGER, -- 1=Sun, ... 7=Sat in_day_cnt IN PLS_INTEGER -- 0-5 ) RETURN PLS_INTEGER; PROCEDURE set_array; END pk_approval;

Package Body

 CREATE OR REPLACE PACKAGE BODY pk_approval AS TYPE num_array IS TABLE OF NUMBER; -- Declare an instance of type num_array -- (to be initialized in set_array) off_array num_array; -- ***************************** -- ** a p p r o v a l _ d t ** -- ***************************** FUNCTION approval_dt ( in_event_dt IN DATE, -- Starting date in_day_cnt IN NUMBER -- Number of "work" days ) RETURN DATE IS day_cnt PLS_INTEGER := in_day_cnt; day_5_cnt PLS_INTEGER; -- day_cnt, mod 5 dow_val PLS_INTEGER; -- 1=Sun, 2=Mon, ..., 7=Sat off_cnt PLS_INTEGER; -- # of actual days in future BEGIN -- "Normalize" day_cnt (>= 0) IF day_cnt < 0 THEN day_cnt := 0; END IF; -- Compute day_5_cnt := MOD (day_cnt, 5); off_cnt := ((day_cnt - day_5_cnt) / 5) * 7; dow_val := TO_NUMBER ( TO_CHAR ( in_event_dt, 'D' ) ); off_cnt := off_cnt + approval_n (dow_val, day_5_cnt); RETURN in_event_dt + off_cnt; END approval_dt; -- *************************** -- ** a p p r o v a l _ n ** -- *************************** -- approval_n returns the number of actual days you -- have to wait for in_day_cnt work days starting -- on weekday in_dow_val. -- For example, approval_n (1, 4) returns 6, -- because starting with Sunday (day 1) the 4th -- workday is 6 actual days away. FUNCTION approval_n ( in_dow_val IN PLS_INTEGER, -- 1=Sun, ... 7=Sat in_day_cnt IN PLS_INTEGER -- 0-5 ) RETURN PLS_INTEGER IS sub_val PLS_INTEGER -- subscript in off_array := in_dow_val + (7 * in_day_cnt); BEGIN RETURN off_array (sub_val); END approval_n; -- ************************* -- ** s e t _ a r r a y ** -- ************************* -- set_array initializes the package variable off_array, -- used in approval_n. PROCEDURE set_array IS BEGIN off_array := num_array ( -- Sun Mon Tue Wed Thu Fri Sat 0, 0, 0, 0, 2, 1, 0, -- 0 days later 1, 1, 1, 3, 3, 2, 1, -- 1 2, 2, 4, 4, 4, 3, 2, -- 2 3, 5, 5, 5, 5, 4, 3, -- 3 6, 6, 6, 6, 6, 5, 4 -- 4 ); END set_array; -- The following part of the package is executed once -- per session, when the package is first loaded. BEGIN set_array; END pk_approval;


This was first published in September 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close