Testing for holidays, part 1

I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my...

holiday table?

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'
);