An infinite date table
Here is the creation script for a neat function that will generate a table full of every date in the given range. -- ============================================= -- Author: Aaron D. Wells -- Create date: 9/23/2010 -- Description: Get a list of dates in a range -- ============================================= CREATE FUNCTION fn_Dates (@fromDate date, @toDate date) RETURNS @dateTable TABLE( [Date] Date NOT NULL, FiscalYear int NOT NULL, FiscalMonth int NOT NULL ) AS BEGIN WITH CTE_DatesTable([date]) AS ( SELECT @fromDate AS [date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) 9 THEN YEAR(date) + 1 ELSE YEAR(date) END FiscalYear, CASE WHEN MONTH(date) > 9 THEN MONTH(date) - 9 ELSE MONTH(date) + 3 END FiscalMonth FROM CTE_DatesTable OPTION (MAXRECURSION 0) RETURN END Using the function is pretty simple, just give it a starting and ending date. By the w...