This piece of SQL code uses Teradata’s in-built System Calendar table to calculate the national holidays for US and Canada.
SELECT
Calendar_Date,
day_of_week,
day_of_week_name,
day_of_month,
day_of_year,
day_of_calendar,
weekday_of_month,
week_of_month,
week_of_year,
week_of_calendar,
month_of_quarter,
c.month_of_year,
month_of_calendar,
quarter_of_year,
quarter_of_calendar,
c.year_of_calendar,
case
when extract(month from Calendar_Date ) = 11 and TD_DAY_OF_WEEK(Calendar_Date) = 5 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 4 then 'Thanksgiving Day (USA)'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 25 then 'Christmas Day'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Christmas Day (in Leiu)' /*26th December is a Monday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Christmas Day (in Leiu)' /*27th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 then 'Boxing Day'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 'Boxing Day (in Leiu)' /*28th December is a Tuesday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Boxing Day (in Leiu)' /*28th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 'Boxing Day (in Leiu)' /*27th December is Tuesday then holiday*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 4 then 'Independence Day'
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 1 then 'New Years Day'
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'New Years Day' /*1st Jan was Sunday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'New Years Day' /*1st Jan was Saturday*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 'Memorial Day' /*Needs fix - Needs to be last Monday of May - Follow lines of Canada's Victoria Day shown in the left join below*/
when extract(month from Calendar_Date ) = 9 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 'Labour Day' /*First Monday of September*/
when extract(month from Calendar_Date ) = 2 and extract(day from Calendar_Date ) = 14 then 'Valentines Day'
when extract(month from Calendar_Date ) = 3 and extract(day from Calendar_Date ) = 17 then 'Saint Patricks Day'
when extract(month from Calendar_Date ) = 1 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 and extract(year from Calendar_Date ) >= 1983 then 'Martin Luthor King Jr Day' /*Third Monday of January*/
when extract(month from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 'Presidents Day' /*Third Monday of Feb*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 2 then 'Mothers Day' /*Second Sunday of May*/
when extract(month from Calendar_Date ) = 6 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 'Fathers Day' /*Third Sunday of June*/
when extract(month from Calendar_Date ) = 10 and extract(day from Calendar_Date ) = 31 then 'Halloween'
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 11 then 'Remembrance Day'
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 12 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Remembrance Day (in Lieu)' /*11th Nov was Sunday*/
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 13 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Remembrance Day (in Lieu)' /*11th Nov was Saturday*/
/*Easter and Good Friday*/
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') then 'Easter Sunday'
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') - 2 then 'Good Friday'
else '' end as Holiday_Name_US,
case
when extract(month from Calendar_Date ) = 11 and TD_DAY_OF_WEEK(Calendar_Date) = 5 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 4 then 'Thanksgiving Day (CANADA)'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 25 then 'Christmas Day'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Christmas Day (in Leiu)' /*26th December is a Monday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Christmas Day (in Leiu)' /*27th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 then 'Boxing Day'
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 'Boxing Day (in Leiu)' /*28th December is a Tuesday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Boxing Day (in Leiu)' /*28th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 'Boxing Day (in Leiu)' /*27th December is Tuesday then holiday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 1 then 'New Years Day'
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'New Years Day' /*1st Jan was Sunday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'New Years Day' /*1st Jan was Saturday*/
when extract(month from Calendar_Date ) = 9 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 'Labour Day' /*First Monday of September*/
when extract(month from Calendar_Date ) = 2 and extract(day from Calendar_Date ) = 14 then 'Valentines Day'
when extract(month from Calendar_Date ) = 3 and extract(day from Calendar_Date ) = 17 then 'Saint Patricks Day'
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 2 then 'Mothers Day' /*Second Sunday of May*/
when extract(month from Calendar_Date ) = 6 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 'Fathers Day' /*Third Sunday of June*/
when extract(month from Calendar_Date ) = 10 and extract(day from Calendar_Date ) = 31 then 'Halloween'
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 11 then 'Remembrance Day'
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 12 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Remembrance Day (in Lieu)' /*11th Nov was Sunday*/
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 13 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Remembrance Day (in Lieu)' /*11th Nov was Saturday*/
/*Second Last Monday in May - Victoria day*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = (max_occurance_of_mon - 1) then 'Victoria Day' /*Second Last Monday in May*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 1 then 'Canada Day'
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Canada Day' /*Canada Day was Sunday*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 'Canada Day'/*Canada Day was Saturday*/
/*Easter and Good Friday*/
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') then 'Easter Sunday'
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') - 2 then 'Good Friday'
when extract(month from Calendar_Date ) = 8 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 'Civic Holiday' /*First Monday of August*/
else '' end as Holiday_Name_CAN,
case
when extract(month from Calendar_Date ) = 11 and TD_DAY_OF_WEEK(Calendar_Date) = 5 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 4 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 25 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1/*26th December is a Monday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1/*27th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 1 /*28th December is a Tuesday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*28th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 1 /*27th December is Tuesday then holiday*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 4 then 1
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 1 then 1
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*1st Jan was Sunday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*1st Jan was Saturday*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 1 /*Needs fix - Needs to be last Monday of May*/
when extract(month from Calendar_Date ) = 9 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 1 /*First Monday of September*/
when extract(month from Calendar_Date ) = 2 and extract(day from Calendar_Date ) = 14 then 1
when extract(month from Calendar_Date ) = 3 and extract(day from Calendar_Date ) = 17 then 1
when extract(month from Calendar_Date ) = 1 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 and extract(year from Calendar_Date ) >= 1983 then 1 /*Third Monday of January*/
when extract(month from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 1 /*Third Monday of Feb*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 2 then 1 /*Second Sunday of May*/
when extract(month from Calendar_Date ) = 6 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 1 /*Third Sunday of June*/
when extract(month from Calendar_Date ) = 10 and extract(day from Calendar_Date ) = 31 then 1
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 11 then 1
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 12 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*11th Nov was Sunday*/
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 13 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*11th Nov was Saturday*/
/*Easter and Good Friday*/
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') then 1
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') - 2 then 1
else 0 end as Holiday_US_Flag,
case
when extract(month from Calendar_Date ) = 11 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 2 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 25 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*26th December is a MOnday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*27th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 26 then 1
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 1 /*28th December is a Tuesday then Holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 28 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*28th December is Monday then holiday*/
when extract(month from Calendar_Date ) = 12 and extract(day from Calendar_Date ) = 27 and TD_DAY_OF_WEEK(Calendar_Date) = 3 then 1 /*27th December is Tuesday then holiday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 1 then 1
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*1st Jan was Sunday*/
when extract(month from Calendar_Date ) = 1 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*1st Jan was Saturday*/
when extract(month from Calendar_Date ) = 9 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 1 /*First Monday of September*/
when extract(month from Calendar_Date ) = 2 and extract(day from Calendar_Date ) = 14 then 1
when extract(month from Calendar_Date ) = 3 and extract(day from Calendar_Date ) = 17 then 1
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 2 then 1 /*Second Sunday of May*/
when extract(month from Calendar_Date ) = 6 and TD_DAY_OF_WEEK(Calendar_Date) = 1 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 3 then 1 /*Third Sunday of June*/
when extract(month from Calendar_Date ) = 10 and extract(day from Calendar_Date ) = 31 then 1
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 11 then 1
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 12 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*11th Nov was Sunday*/
when extract(month from Calendar_Date ) = 11 and extract(day from Calendar_Date ) = 13 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*11th Nov was Saturday*/
/*Second Last Monday in May - Victoria day*/
when extract(month from Calendar_Date ) = 5 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = (max_occurance_of_mon - 1 ) then 1 /*Second Last Monday in May*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 1 then 1
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 2 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1/*Canada Day was Sunday*/
when extract(month from Calendar_Date ) = 7 and extract(day from Calendar_Date ) = 3 and TD_DAY_OF_WEEK(Calendar_Date) = 2 then 1 /*Canada Day was Saturday*/
/*Easter and Good Friday*/
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') then 1
when Calendar_Date = cast((concat(trim(extract(year from Calendar_Date)), LPAD(TRIM(((3 + ( ((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))),2,'0'), LPAD(TRIM(((((24+ (19 * (extract(year from Calendar_Date) mod 19))) mod 30) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 28 - (31 * ((( 3 + (((( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - ((extract(year from Calendar_Date) + extract(year from Calendar_Date) / 4 + ( ( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) - (( (24 + (19 * (extract(year from Calendar_Date) mod 19) ) ) mod 30 ) / 28) ) - 13) mod 7)) + 40) / 44)))/4))),2,'0')) ) as dateformat 'yyyymmdd') - 2 then 1
when extract(month from Calendar_Date ) = 8 and TD_DAY_OF_WEEK(Calendar_Date) = 2 and TD_WEEKDAY_OF_MONTH(Calendar_Date) = 1 then 1 /*First Monday of August*/
else 0 end as Holiday_Canada_Flag
FROM sys_calendar.calendar c
LEFT JOIN
(
/*How many Mondays are there in each month - Required for Victoria Day*/
SELECT Year_of_Calendar, month_of_year, max( TD_WEEKDAY_OF_MONTH(Calendar_Date)) as max_occurance_of_mon
FROM sys_calendar.calendar
WHERE TD_DAY_OF_WEEK(Calendar_Date) = 2
GROUP BY
Year_of_Calendar, month_of_year
) MONDAYS_IN_MONTH
ON MONDAYS_IN_MONTH.Year_of_Calendar = c.Year_of_Calendar
AND MONDAYS_IN_MONTH.month_of_year = c.month_of_year
Advertisements
