I have created a function that, given a date and a number, adds that many days to the date. The catch is that it has parameters to include Saturday, Sunday, or Monday as those days. Here is an example of the call
DAYSBETWEEN('01-AUG-11',5,0,0,0) would give you 5 Calendar days from Aug 1, 2011 (as you would not exclude Saturday, Sunday, or Monday)
DAYSBETWEEN('01-AUG-11', 5, 1,1,0) would give you 5 business days from Aug 1, 2011
DAYSBETWEEN('01-AUG-11',5,0,1,1) would give you 5 days from Aug 1, 2011 excluding Sunday and Monday.
My current function is:
create or replace
FUNCTION DAYSBETWEEN(
DAYIN IN DATE ,
ADDNUM IN NUMBER ,
EXSAT IN NUMBER ,
EXSUN IN NUMBER ,
EXMON IN NUMBER )
RETURN DATE
IS
dtestart DATE;
intcount NUMBER;
holidays NUMBER;
BEGIN
dtestart := DAYIN;
intcount :=1;
WHILE intcount <= ADDNUM
LOOP
dtestart := dtestart + 1;
IF NOT((EXSAT = 1 AND TO_CHAR(dtestart, 'd')= 7) OR (EXSUN=1 AND TO_CHAR(dtestart, 'd')= 1) OR (EXMON=1 AND TO_CHAR(dtestart, 'd')= 2)) THEN
intcount := intcount + 1;
END IF;
END LOOP;
RETURN dtestart;
END DAYSBETWEEN;
This works for a small set of values for the date, but when there is a larger set of dates, it takes hours to complete due to the while loop and the multiple or statements.
Is there another set of logic I could use to improve this query, possibly not using the while loop/or statements?