Please find below a calendar creation script for Microsoft SQL and Data Warehouse implementations.
One of the extremely useful features of this is the stored calculation of various data attributes (eg. Quarter, Day of Week, Holidays, Work Days). This eliminates the need for complex SQL, transformations in ETL or aggregation code resulting in greatly improved performance for databases and business intelligence applications.
The script will step through the following:
Create a schema named "reference"
Create a function for the calculation of Easter holidays (it's complicated how they come up with it, so this makes it easier)
Create four tables named "calendar", "public_holidays", "timezone" and "timezone_adjustment_rule"
Populate the calendar with base calendar data, the default is 1st Jan 1900 to 31st Dec 2099 and this can be changed in the declaration portion of the script
Process and update the calendar attributes
Populate the public holiday table (I have it defaulted to QLD, Australia, but you can modify this section how you like)
Update the calendar table with the public holiday dates and names (defaulted to QLD, Australia, but you can change how you want the holidays processed in the declaration portion of the script)
Process and update the timezone and daylight savings attributes from populated timezone information
If you wish you can add custom public holiday dates to the public_holiday table, the script, when it runs again will only purge the public holiday entries that have "system" as the source.
To run the script, open the SQL file in SQL Server Management Studio, select the database you want to run the script against, verify your declared variables and execute. The script has also been configured to run multiple times, if needed.
