Quite often I have a need to generate reports that show the days of a month with counts, sums, averages, etc by day. I also get a lot of requests to show each of the days of the month regardless of the fact there might be any data for that day.
I have gone down the route of creating a table, putting in +/- 100 years of dates or whatever and then done a join but I tried some code from this article that creates a CTE dates table for the values I specify. I have shown the code here...
WITH CTE_DatesTable AS ( SELECT CAST('20090601' as datetime) AS [date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) <= '20090630' ) SELECT [date] FROM CTE_DatesTable OPTION (MAXRECURSION 0);
This uses a recursion to generate but even when I specified a from date of '20000101' and an end date of '20100219' it ran in under a second.
Would this be a better method than holding large tables of dates on the server? Pros/cons please guys.