Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Dimension Expand / Collapse
Author
Message
Posted Sunday, April 20, 2008 2:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 16, 2013 4:57 PM
Points: 75, Visits: 168
Comments posted to this topic are about the item Date Dimension
Post #487628
Posted Wednesday, June 4, 2008 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2008 9:13 AM
Points: 1, Visits: 22
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
Post #511300
Posted Wednesday, January 11, 2012 10:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:33 AM
Points: 832, Visits: 1,593
You're both committing unnecessary RBAR.



One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1234527
Posted Thursday, January 12, 2012 4:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 11:54 AM
Points: 819, Visits: 2,116
For one time populations, there shouldn't be an issue with RBAR.


Post #1234644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse