Recently I needed configurable grouping in an SQL statement - the exact requirement was that the results be grouped by day, week or month, depending on the user's selection.
This can be done without a table of dates or dynamic SQL by using a CASE statement in the GROUP BY clause.
Here's an example with CASE to achieve configurable date grouping using the "TransactionHistory" table in AdventureWorks:
--"group by" variable
DECLARE @GroupBy TINYINT
--******** CHANGE GROUPING HERE ********
--group by Day (0), Week (1) or Month (2)
SET @GroupBy = 2
--**************************************
--need to set the first day of week to be Monday so...