A while back, I wrote an article called Creating a date dimension or calendar table in SQL Server. I have used this pattern repeatedly and, based on the questions I get from the community, many of you are using it, too. Some of the questions I get are along the lines of “how do I actually use this table in my queries?” and “what are the performance characteristics compared to other approaches?” So, I thought I would put together a collection of use cases and analysis, starting with business day problems.
I’m a big fan of calendar tables as well. They’re quite useful for a variety of business problems and make date math problems really easy, especially when dealing with non-standard calendars (e.g., work weeks, fiscal years, figuring out what day Easter is).