I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my...
CREATE TABLE holiday ( id NUMBER (10) PRIMARY KEY, start_dt DATE NOT NULL, end_dt DATE NOT NULL, cmnt VARCHAR2 (100) );
I've attached some sample data for the table.
You've found a convenient way to store your holiday data, I assume you also have a convenient way to retrieve it, such as a function that takes a date as an argument and returns one value (e.g. a positive number) if that date is a holiday, or another value (e.g. 0) if it's not a holiday. If you have such a function, simply add it to the IF statement that tests if you want to skip a date:
CREATE OR REPLACE FUNCTION approval_dt ( in_event_dt IN DATE, -- Starting date in_day_cnt IN NUMBER -- Number of "work" days ) RETURN DATE IS BEGIN IF TO_CHAR (in_event_dt, 'Dy') IN ('Thu', 'Fri') OR holiday_num (in_event_dt) > 0 THEN -- On off-days, skip ahead with same count RETURN approval_dt ( in_event_dt + 1, in_day_cnt ); ELSIF in_day_cnt <= 0 THEN -- this is the day wanted RETURN in_event_dt; ELSE -- skip ahead and count down RETURN approval_dt ( in_event_dt + 1, in_day_cnt - 1 ); END IF; END;
If you don't have a function like that, see the next page.
INSERT INTO holiday ( id, start_dt, end_dt, cmnt ) VALUES ( holiday_id_seq.NEXTVAL, '13-Oct-2003', '13-Oct-2003', 'Columbus Day' ); INSERT INTO holiday ( id, start_dt, end_dt, cmnt ) VALUES ( holiday_id_seq.NEXTVAL, '27-Nov-2003', '28-Nov-2003', 'Thanksgiving (Thursday); business closed Friday, too' );
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.