I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.