-- Holidays that are designated as first Monday, fourth Thursday etc. can be computed as
-- shown below using the analytic function ROW_NUMBER(). The third Monday of January is
-- Martin Luther King Day in USA. In 2014, it is January 20th 2014.
WITH q_date AS
(SELECT &i_yyyymm i_yyyymm FROM DUAL),
CONNECT BY level <= mth_days),
ROW_NUMBER() OVER (ORDER BY wk_day) day_cnt
WHERE wk_day = INITCAP('&i_3ltr_day')) -- Mon, Tue, etc.
WHERE day_cnt = &i_cnt;
-- Third Monday of February is President's day and it is February 17th, 2014. Using
-- the same query, the first Monday of September is Labor Day in USA, which is
-- computed as September 1st, 2014.
Output: Other Similar Holidays