April 20, 2008 at 2:43 am
Comments posted to this topic are about the item Date Dimension
June 4, 2008 at 6:31 am
I like a cte better.. something like
WITH mycte
AS (
SELECT
CAST('2000-01-1' AS DATETIME) DateValue
UNION ALL
SELECT
DateValue + 1
FROM
mycte
WHERE
DateValue + 1 < '2049-12-31'
)
INSERT INTO
[dbo].[DimDate]
(
[Date]
, [DayNumberOfYear]
, [DayNameOfWeek]
, [DayOfWeek]
, [DayOfMonth]
, [WeekNumberOfYear]
, [MonthNumberOfYear]
, [MonthName]
, [CalendarQuarter]
, [CalendarYear]
, [QuarterName]
)
SELECT
DateValue AS [DATE]
, DATEPART(dy, DateValue) [day of year]
, DATENAME(dw, DateValue) [Day]
, DATEPART(dw, DateValue - 1) [day of week]
, DATEPART(dd, DateValue) [day of month]
, DATEPART(ww, DateValue) [week]
, DATEPART(mm, DateValue) [month]
, DATENAME(mm, DateValue) [month]
, DATEPART(qq, DateValue) [quarter]
, DATEPART(yy, DateValue) [year]
, 'Q' + CAST(DATEPART(qq, DateValue) AS CHAR(1)) [quarterName]
FROM
mycte
OPTION
( MAXRECURSION 0 )
will generate the table in one statement and seems to be fairly fast
January 11, 2012 at 10:52 pm
You're both committing unnecessary RBAR.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
January 12, 2012 at 4:17 am
For one time populations, there shouldn't be an issue with RBAR.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply