I feel the need to add a personal opinion here but please don't think I am saying there is a right or a wrong way. In the words of Dirty Harry "opinions are like Ar*eho les - everyone has one"

As I stated earlier, I prefer the method of creating a calendar table and then linking the data in to populate it, which results in NULL's for the unmatched data. What I did not state was why. So here's why...

It is readable and does not contain functions.

I have been managing teams of sql and application developers for years and I have seen many brilliant and highly effective but totally un-maintainable chunks of sql. Breaking the problem down in to smaller, more manageable and sometime bleedin' obvious parts is generally a good thing as the next developer to work on the code may not have the same level of coding competence or ability to understand complexities quickly. Sure the clever code might run slightly faster and if performance is a problem then perhaps it is the way forward but ease of change should always be the top goal (IMHO)

I also hate the use of functions where they can be avoided. Adding a function means that people often have to lookup what the function does and how it works (sometimes having to ask "Uncle Google"). Don't get me wrong here, I love functions but only where required. Functions can make a large chunk of SQL much easier to read and use in some cases. Personally I don't think they do here.

I have a number table called Numbers on my system with a single column called Number and indexed values from 0 to 99,999. I use this table for all sorts of string and date manipulations. I also have a permanent and fully indexed calendar table too. These two tables are used all over the place and are highly effective.

The other great feature of a permanent calendar table is that you can add special dates and slowly changing dimension tables to them too.

So there are my reasons.

Hope this doesn't offend. Please remember this is only an opinion.

Dave