I needed a similar feature a few years ago and came up with 8 classifications of International holidays (non-working days) as either fixed or relative to some other date, one of which, I think, is unique to the UK. I handled holidays as single days and haven't checked if your multiple day code will cope with this. I assume it will!

The 8 are:

0 -- fixed date every year (MMDD)

1 -- days relative to Easter (e.g. Whitmonday is Easter Sunday + 50 days)

2 -- Fixed, but if it's at a weekend, it's replaced by the following Monday

3 -- Specific day of week, usually Moday of first (MM01) or last (MM25) week of a month, but could be e.g. Wednesday before MMDD

4 -- As 2, but for the 2nd day of a pair (e.g. Christmas/Boxing days in the UK)

5 -- fixed date in Hijri (Moslem) Calendar

6 -- days relative to Greek Orthodox Easter

7 -- relative to previous winter solstice (Chinese Qing Ming Jie)

The awkward item was 4, UK's Boxing Day holiday. Usually it's considered to be 26th December, but if it falls on a Saturday then it's replaced by havig the Monday (28th) as a holiday. However, if it falls on Sunday or Monday, then it's replaced by Tuesday (27/28th) because Christmas Day will be on Saturday/Sunday and hence will have triggered Monday already being a non-working day. So far, this is the only example I've found where a 2-day holiday gives 2 non-working days. Many countries assume that if a 'religious' holiday falls at a weekend, then no extra days are substituted.

I also collapsed the Easter calculation (for 1900 to 2099) into a single inline calculation (which needed a little tweaking):

when 1 -- days relative to Easter (e.g. Whitmonday is Easter Sunday + 50 days)

then

dateadd(d,(case @year when 1954 then 14 when 1981 then 14 when 2049 then 14 when 2076 then 14 else 21 end

+ ((19 * (@year % 19) + 24 ) % 30) + ((2 * (@year % 4) + 4 * (@year % 7) + 6 * ((19 * (@year % 19) +24 ) % 30) + 5) % 7))

+[MonthDayOffset],convert(char(4),@year)+'0301')

I also used a single field as MonthDayOffset, i.e. it either contains a month & day (1225, 0704) or an offset (-2 for Good Friday, 2 days before Easter). This was simply because I could not see any point in a table having 2 columns where either one or the other but never both would be used.

Interesting to see how someone else solved a similar problem.