 ; 
 
 
--- Monday on or before May 24	Victoria Day	Fête de la Reine
+-- -- Monday on or before May 24	Victoria Day	Fête de la Reine
+-- WITH cte AS (
+--     SELECT EXTRACT(YEAR FROM dc2.calendar_date) AS yr,
+--            MAX(calendar_date) AS mon_may_24
+--            FROM dim_calendar AS dc2
+--            WHERE EXTRACT(MONTH FROM calendar_date) = 5
+--            AND   EXTRACT(DAY FROM celndar_date) <= 24
+--            AND EXTRACT( DOW FROM calendar_date) = 1
+--            GROUP BY EXTRACT(YEAR FROM dc2.calendar_date)
+-- )
+-- UPDATE dim_calendar
+-- SET hol_ca_nl = TRUE
+-- FROM cte
+-- WHERE dim_calendar.calendar_date = cte.mon_may_24
+-- ;
 -- 
--- 
+--June 24	Discovery Day / National Holiday	Journée découverte / Fête nationale du Québec / Saint-Jean-Baptiste
+UPDATE dim_calendar
+SET hol_ca_nl = TRUE
+WHERE EXTRACT( DAY   FROM calendar_date) = 24
+AND   EXTRACT( MONTH FROM calendar_date) = 6
+; 
+
 -- July 12	Orangemen's Day	Fête des orangistes
 UPDATE dim_calendar
 SET hol_ca_nl = TRUE

BEGIN;

-- Canada AB

UPDATE dim_calendar
SET hol_ca_nl = FALSE
;


-- Family Day	Fête de la famille
-- Second Monday in February	
WITH cte AS (
    SELECT EXTRACT(YEAR FROM dc2.calendar_date) AS yr,
           MIN(calendar_date) AS second_mon_feb
           FROM dim_calendar AS dc2
           WHERE EXTRACT(MONTH FROM calendar_date) = 2
           AND EXTRACT( DAY FROM calendar_date > 7
           AND EXTRACT( DOW FROM calendar_date) = 1
           GROUP BY EXTRACT(YEAR FROM dc2.calendar_date)
)
UPDATE dim_calendar
SET hol_ca_nl = TRUE
FROM cte
WHERE dim_calendar.calendar_date = cte.second_mon_feb
;


-- Islander Day	Fête des Insulaires/Family Day	Fête de la famille/Heritage Day	Fête du Patrimoine
-- Third Monday in February	
WITH cte AS (
    SELECT EXTRACT(YEAR FROM dc2.calendar_date) AS yr,
           MIN(calendar_date) AS third_mon_feb
           FROM dim_calendar AS dc2
           WHERE EXTRACT(MONTH FROM calendar_date) = 2
           AND EXTRACT( DAY FROM calendar_date > 14
           AND EXTRACT( DOW FROM calendar_date) = 1
           GROUP BY EXTRACT(YEAR FROM dc2.calendar_date)
)
UPDATE dim_calendar
SET hol_ca_nl = TRUE
FROM cte
WHERE dim_calendar.calendar_date = cte.third_mon_feb
;


-- Saint Patrick's Day	Jour de la Saint-Patrick
-- March 17	

; 


-- -- December 26	Boxing Day	Lendemain de Noël
-- UPDATE dim_calendar
-- SET hol_ca_nl = TRUE
-- WHERE EXTRACT( DAY   FROM calendar_date) = 26
-- AND   EXTRACT( MONTH FROM calendar_date) = 12
-- ; 
-- 
COMMIT;
