Sorry for the late response... I'm just getting caught up on some newsletters where I've been too busy to read.

I think that you did a great job in this article, except in one place (and I'm somewhat surprised that Jeff didn't mention this).

So, in all of this work to build a calendar table to create faster, more efficient set-based queries, you end up using a loop instead of a set-based method to generate your table... here's a set-based method to generate the calendar table:

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

Thousands (N) AS (SELECT t1.N FROM Tens t1, Tens t2, Tens t3),

Millions (N) AS (SELECT t1.N FROM Thousands t1, Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions),

Months (N, MonthStart) AS (SELECT TOP (25*12)

N, DATEADD(MONTH, N, '1999-12-01T00:00:00')

FROM Tally)

SELECT N,

MonthStart,

NextMonth = DATEADD(MONTH, 1, MonthStart),

MonthDescr = CONVERT(CHAR(3), caMonth.MonthName) + '-' +

RIGHT(CONVERT(CHAR(4), YEAR(MonthStart)),2),

caMonth.MonthName,

YearMonth = (YEAR(MonthStart)*100) + caMonth.MonthNbr,

caMonth.MonthNbr

FROM Months

CROSS APPLY (SELECT MonthNbr = DATEPART(MONTH, Months.MonthStart),

MonthName = DATENAME(MONTH, Months.MonthStart)) caMonth

ORDER BY Months.MonthStart;

You can use SET IDENTITY_INSERT to insert N into the Identity column. And to address the Y2K issue brought up (running out of dates), just change the "25" to 5000 to take it up to year 7000. Now, if queries based on this table are still in use then, I think we've got bigger problems to worry about... :-D:w00t: