### Calculate working days

C

Calculate working days for each month. You can create function from this and adjust your national holidays.

```DECLARE
last_month  VARCHAR2(8);
last_count  PLS_INTEGER;
--
FUNCTION get_easter_monday (
in_year PLS_INTEGER
)
RETURN DATE
AS
a NUMBER;
c NUMBER;
e NUMBER;
g NUMBER;
n NUMBER;
x NUMBER;
BEGIN
c := in_year / 100 + 1;
x := (3 * c / 4) - 12;
g := MOD(in_year, 19) + 1;
e := MOD(11 * g + 15 + ((8 * c + 5) / 25) - x, 30);
--
IF (e = 25 AND g < 11) OR e = 24 THEN
e := e + 1;
END IF;
--
n := 44 - e;
IF n < 21 THEN
n := n + 30;
END IF;
--
a := n + 7 - MOD((5 * in_year / 4) - x - 3 + n, 7);
--
RETURN TRUNC(TO_DATE(TO_CHAR(in_year) || '0301', 'YYYYMMDD') + a);
END;
--
in_date         DATE        := TRUNC(SYSDATE),
in_weekends     PLS_INTEGER := 1,
in_holidays     PLS_INTEGER := 1
)
RETURN NUMBER
AS
list_of_holidays CONSTANT VARCHAR2(256) := '0101,0501,0508,0705,0706,0928,1028,1117,1224,1225,1226';
BEGIN
-- check for weekends
IF in_weekends = 1 AND TO_CHAR(in_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN
-- MONDAY = 1 + TRUNC(in) - TRUNC(in, 'IW')
RETURN -1;
END IF;

-- check for other holidays
IF in_holidays = 1 THEN
IF REGEXP_SUBSTR(list_of_holidays, TO_CHAR(in_date, 'MMDD')) IS NOT NULL THEN
RETURN -2;
END IF;
END IF;

-- check for easter monday
IF in_holidays = 1 AND TO_CHAR(in_date, 'D-MM') IN ('1-03', '1-04') THEN
IF in_date = get_easter_monday(TO_CHAR(in_date, 'YYYY')) THEN
RETURN -3;
END IF;
END IF;
--
RETURN 1;
END;
BEGIN
-- year/month calendar overview with working days
FOR c IN (
SELECT
TRUNC(SYSDATE, 'Y') + LEVEL - 1                     AS date_,
TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month_
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y') + 1
) LOOP
IF last_month != c.month_ OR last_month IS NULL THEN
IF last_month IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(last_month || ' ' || last_count);
END IF;
--
last_month := c.month_;
last_count := 0;
END IF;
--
last_count := last_count + CASE WHEN is_business_day(c.date_) > 0 THEN 1 ELSE 0 END;
-- check what kind of holiday selected date is
--DBMS_OUTPUT.PUT_LINE(TO_CHAR(c.date_, 'YYYY-MM-DD') || ' ' || NULLIF(is_business_day(c.date_), 1));
END LOOP;
END;
/
```