Want to create a SQL Server query using a table created at runtime that just has month numbers and year numbers? I did, so here's how I did it:
DECLARE @StartDate datetime = '9/1/12', @EndDate datetime = '8/1/13'
CREATE TABLE #cal (TheYear int, TheMonth int)
INSERT #cal
SELECT YEAR(dateadd(month, number, @StartDate)), MONTH(dateadd(month, number, @StartDate))
FROM (SELECT DISTINCT number FROM master.dbo.spt_values WHERE name IS NULL) n
WHERE DATEADD(month, number, @StartDate) < @EndDate
You can use a table variable instead, quite easily, but SSRS 2008 doesn't allow them so I used a temp table.