asked
How to derive season in a date dimension
I'm implementing a DataWarehouse date dimension. According to my scenario I have to mark days between Octomber-January as 'Season_A' and days between April-August as 'Season_B'. I have created the 'dimDate' table as below. CREATE TABLE dbo.dimDate ( DateKey INT NOT NULL, FullDate DATE NOT NULL, MonthNumberName NVARCHAR(15) NULL, CalendarQuarter TINYINT NULL, CalendarYear SMALLINT NULL, Season NVARCHAR(10) CONSTRAINT PK_Dates PRIMARY KEY (DateKey) ); GO
You don't appear to have asked a specific question - other than what you put in the title - so I'm not entirely sure what aspect of this you're stuck on, but I'd suggest you look at the CASE and MONTH functions. If that doesn't help, perhaps you can let us know what specifically you're stuck on?
Hi there, You can make use of Computed columns for SEASON. Check this out. CREATE TABLE dbo.dimDate ( DateKey INT NOT NULL, FullDate DATE NOT NULL, MonthNumberName NVARCHAR(15) NULL, CalendarQuarter TINYINT NULL, CalendarYear SMALLINT NULL, Season AS (CASE WHEN DATEPART(MM, FullDate) in (10, 11, 12, 1) THEN 'SEASON_A' -- Oct, Nov, Dec, Jan WHEN DATEPART(MM, FullDate) in (4, 5, 6, 7, 8) THEN 'SEASON_B' END) -- Apr, May, Jun, Jul, Aug CONSTRAINT PK_Dates PRIMARY KEY (DateKey) ) With computed columns, you will also not need to specifically insert the value of Season, because it will automatically compute it when your provide a value for FullDate.
15 People are following this question.
