Computing Holidays

Kevin Feasel

2016-11-07

T-SQL

Gerald Britton has a few ways of calculating holidays for date dimensions:

Notice also that I use CROSS APPLY as an expression evaluator.  This keeps the code a little DRY-er.  In OOP-speak, I’ve encapsulated what varies.

Now that I’ve got a basic date table, let’s see about updating those holiday columns.   I’ll do it with an UPDATE command here, though it is possible to make them computed also — it just gets a little messy since there are so many different holidays with different calculations.  Let’s start with Thanksgiving.  In Canada, Thanksgiving Day is the second Monday in October.  In the US, it’s the fourth Thursday in November.  I’ve seen some interesting approaches to solving the problem.  One (for US Thanksgiving) looks like this:

I like the Easter formula.  Click through for details.

Related Posts

Pivoting Performance Counter Data

Dave Bland shows how you can build a dynamic pivot to see performance counter data over a stretch of time: The next step is to write the code to capture the counter values and insert the data it the temporary table created above.  Because we need to capture the values over a period of time, […]

Read More

Near-Zero Downtime Identity Column Changes

I’m getting close to the end of my series on near-zero downtime deployments. This latest post involves identity column changes: There are some tables where you create an identity value and expect to cycle through data. An example for this might be a queue table, where the data isn’t expected to live permanently but it […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930