Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: