|
So the Approvals Office promises results, sometimes in 0 days, sometimes in 1 day,
and so on, but Office is closed on Thursdays and Fridays.
The simplest solution I can imagine simulates
how you might solve this manually, using a calendar and counting down:
-- approval_dt returns the date that is in_day_cnt "work" days
-- after in_event_dt.
-- A "work" day is any day except Thursday or Friday.
-- If in_day_cnt <= 0, in_event_dt (or the next work day,
-- if in_event_dt is not one) will be returned.
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')
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;
This is a great solution in several ways:
- It works
- You can easily prove that it works
- It's easy to modify (say the off-days change to
Tuesdays and Fridays, or you have to exclude holidays,
or the rules change during the summer months)
In fact, the only thing I can see wrong with this solution is that it's
slow, especially for large values of in_day_cnt. If this is too slow for you,
there is another solution.
Click for part 2 of Frank's response, A faster day counter.
|
