question

Dulshanka avatar image
Dulshanka 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
tsqlproceduredatawarehouse
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
1 Like 1 ·

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.