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 that
--the datepart calculations work
SET DATEFIRST 1
SELECT --apply dynamic grouping based on "@GroupBy"
--note that the "GROUP BY" clause needs to match this CASE statement
[TheDate] = CASE
--day: ignore time by converting to date-only string format then back to date
WHEN @GroupBy = 0 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
TH.[TransactionDate], 106))
--week: get next Sunday after the date by adding (7 - weekday)
--e.g. to a Tuesday (day 2) we add 5 to get Sunday
-- to a Sunday, we add 0
WHEN @GroupBy = 1 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
DATEADD(day, 7 - DATEPART(weekday, TH.[TransactionDate]),
TH.[TransactionDate]), 106))
--month: get first day of month by calculating the current
--day of month minus (the date minus 1)
--e.g. on the 5th of the month, minus 4
-- on the first of the month, minus 0
WHEN @GroupBy = 2 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
DATEADD(day, -(DATEPART(day, TH.[TransactionDate]) - 1),
TH.[TransactionDate]), 106))
END,
--some value here
COUNT(TH.[TransactionID]) AS [NumberOfTransactions]
FROM AdventureWorks.Production.[TransactionHistory] TH WITH (NOLOCK)
GROUP BY
CASE
WHEN @GroupBy = 0 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
TH.[TransactionDate], 106))
WHEN @GroupBy = 1 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
DATEADD(day, 7 - DATEPART(weekday, TH.[TransactionDate]),
TH.[TransactionDate]), 106))
WHEN @GroupBy = 2 THEN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(25),
DATEADD(day, -(DATEPART(day, TH.[TransactionDate]) - 1),
TH.[TransactionDate]), 106))
END
ORDER BY 1 ASC