-- Populate a 1,200 row calendar table
CREATE TABLE	#Calendar
		(
			FromDate DATE NOT NULL,
			ToDate DATE NOT NULL,
			YearMonth INT NOT NULL
		)

INSERT		#Calendar
		(
			FromDate,
			ToDate,
			YearMonth
		)
SELECT		DATEADD(MONTH, m.Number, DATEADD(YEAR, y.Number, '20000101')),
		DATEADD(MONTH, 1 + m.Number, DATEADD(YEAR, y.Number, '20000101')),
		100 * (2000 + y.Number) + m.Number + 1
FROM		master..spt_values AS y
INNER JOIN	master..spt_values AS m ON m.Type = 'P'
			AND m.Number BETWEEN 0 AND 11
WHERE		y.Type = 'P'
		AND y.Number BETWEEN 0 AND 99

CREATE UNIQUE CLUSTERED INDEX UCX_Calendar ON #Calendar (FromDate, ToDate, YearMonth)

-- Populate a 8,000,000 row orders table
CREATE TABLE	#Orders
		(
			CustomerID INT NOT NULL,
			OrderDate DATETIME NOT NULL,
			Amount MONEY NOT NULL
		)

INSERT		#Orders
		(
			CustomerID,
			OrderDate,
			Amount
		)
SELECT		ABS(CHECKSUM(NEWID())),
		DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 60, DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 52596000, '20000101')),
		ABS(CHECKSUM(NEWID())) / 10000E
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.Type = 'P'
			AND v2.Number BETWEEN 0 AND 199
INNER JOIN	master..spt_values AS v3 ON v3.Type = 'P'
			AND v3.Number BETWEEN 0 AND 199
WHERE		v1.Type = 'P'
		AND v1.Number BETWEEN 0 AND 199

CREATE UNIQUE CLUSTERED INDEX UCX_Orders ON #Orders (CustomerID, OrderDate)

-- Solution by Peso
SELECT	DATEADD(MONTH, theMonth, 0) AS YearMonth,
	theAmount AS Amount
FROM	(
		SELECT		DATEDIFF(MONTH, 0, OrderDate) AS theMonth,
				SUM(Amount) AS theAmount
		FROM		#Orders
		GROUP BY	DATEDIFF(MONTH, 0, OrderDate)
	) AS d
/*
(1200 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0.
Table '#Orders'. Scan count 17, logical reads 31849.

 SQL Server Execution Times:
   CPU time = 5772 ms,  elapsed time = 388 ms.
*/

-- Solution by Celko
SELECT		c.YearMonth,
		SUM(o.Amount) AS Amount
FROM		#Calendar AS c
INNER JOIN	#Orders AS o ON o.OrderDate >= c.FromDate
			AND o.OrderDate < c.ToDate
GROUP BY	c.YearMonth
/*
(1200 row(s) affected)
Table 'Worktable'. Scan count 1200, logical reads 24162377.
Table '#Calendar'. Scan count 17, logical reads 13.
Table '#Orders'. Scan count 1, logical reads 31849.

 SQL Server Execution Times:
   CPU time = 33025 ms,  elapsed time = 31425 ms.
*/
DROP TABLE	#Calendar,
		#Orders