Add ISO 3166-2 code compatibility to allow the specification of a country's principal subdivisions

[ericdomond]

The feature request

Currently, the HolidaysDefinition documentation indicates that only two-character strings will be allowed in IsoCountry. SQLBI's blog post also suggests enhancing the template by including region-specific holidays, however since some holidays are specific to a particular region, IsoCountry should be adjusted to allow for the ISO 3166-2 code that includes regions. (e.g. "CA" is the code for Canada, but "CA-AB" is the code for the province of Alberta within Canada.)

Proposed solution

Once users can input ISO 3166-2 codes, they can enhance the existing HolidaysDefinition definition to incorporate region-specific holidays. There would also need to be some kind of inheritance when country-wide holidays still apply to the selected region and simply use the two-character ISO 3166 code. (e.g. Selecting "Canada (Alberta)" would use all holidays with both "CA" and "CA-AB" when defined in IsoCountry) Otherwise, all federal holidays would need to be duplicated for each region.

Additional context

No response

[marcosqlbi]

The idea is good, but I have a few doubts about implementing it.
Because the Date table can have only one holiday per date, introducing local holidays in the generic template would create a few issues:

• there are cases where the same date has different holidays at different levels (country/state/city); we could prioritize the highest level, but it could be not satisfying for every use case;
• only one "local" definition could be used. Therefore, you should use either "CA" or "CA-ON" or "CA-AB", without having the possibility of choosing "CA-ON"+"CA-AB", because that would raise the issue of defining which day is a working day or not and where - something that would require a more complex model and it's outside of the scope for the generic Date table.

This is the reason why we choose to stop the definition at the country level and leave users the ability to customize their holidays' definition including local holidays as they prefer.

Thus, my question to you is the following: how would you like to use them if you had multiple states? Would you be happy to choose a single state, or would you be able to use multiple states for the same Date table?

[ericdomond]

For my use case I would only ever use a single state/province. (Apologies for the late reply!)