How to Create Dim Dates Table via T-SQL Query

Microsoft SQL Server Release with Sample database Adventure Works (OLTP, DW).  These sample database (AdventureWorks, AdvetureWorksDW) can be download on www.codeplex.com. In this article we will create customize DImDates Table. As you already know DimDates are one of table in AdventureWorksDW database. Here is sample script:
— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Create customize DimDates Table
— =============================================
IF EXISTS(select TOP 1 1 From sys.sysobjects Where id=OBJECT_ID(‘DimDates’) and xtype=’U’)
BEGIN
Drop Table DimDates
END
GO
CREATE TABLE dbo.DimDates(
DateKey int NOT NULL,
FullDateKey date NOT NULL,
DayNumberOfWeek tinyint,
DayNameOfWeek nvarchar(10),
INA_DayNameOfWeek nvarchar(10),
DayNumberOfMonth tinyint,
DayNumberOfYear smallint,
WeekNumberOfYear tinyint,
EnglishMonthName nvarchar(10),
INAMonthName nvarchar(10),
MonthNumberOfYear tinyint,
CalendarQuarter tinyint,
CalendarYear smallint,
CalendarSemester tinyint,
FiscalQuarter tinyint,
FiscalYear smallint,
FiscalSemester tinyint
) ON [PRIMARY]
GO
ALTER TABLE DimDates ADD CONSTRAINT PK_DimDate_FullDateKeys PRIMARY KEY CLUSTERED(FullDateKey) on [PRIMARY]
GO
ALTER TABLE DimDates ADD CONSTRAINT [AK_DimDate_DateKeys] UNIQUE NONCLUSTERED(DateKey ASC)ON [PRIMARY]
GO

— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Create Stored Procedure to Populate DimDates Table
— =============================================

IF OBJECT_ID (N’dbo.usp_CreateDimDates’, N’P’) IS NOT NULL
DROP PROCEDURE dbo.usp_CreateDimDates;
GO

CREATE Procedure dbo.usp_CreateDimDates(@StartDate Smalldatetime, @EndDate Smalldatetime)
AS
DECLARE @DimDates TABLE
(
DateKey int,
FullDateKey date Primary Key,
DayNumberOfWeek tinyint,
DayNameOfWeek nvarchar(10),
INA_DayNameOfWeek nvarchar(10),
DayNumberOfMonth tinyint,
DayNumberOfYear smallint,
WeekNumberOfYear tinyint,
EnglishMonthName nvarchar(10),
INAMonthName nvarchar(10),
MonthNumberOfYear tinyint,
CalendarQuarter tinyint,
CalendarYear smallint,
CalendarSemester tinyint,
FiscalQuarter tinyint,
FiscalYear smallint,
FiscalSemester tinyint
)
Declare @FiscalDate smalldatetime, @LoopDate smalldatetime
SET @LoopDate=@StartDate
BEGIN
WHILE @EndDate>=@LoopDate
BEGIN
SET @FiscalDate=Dateadd(Month, 6, CAST(CAST(DATEPART(Year, @LoopDate) as nvarchar(4))+’/’+
RIGHT(‘0′ + CAST(DATEPART(Month, @LoopDate) as nvarchar(2)), 2)+’/1’ AS smalldatetime))

INSERT @DimDates
SELECT
CAST(CAST(DATEPART(Year, @LoopDate) as nvarchar(4))+
RIGHT(‘0’ + CAST(DATEPART(Month, @LoopDate) as nvarchar(2)), 2)+
RIGHT(‘0’ + CAST(DATEPART(Day, @LoopDate) as nvarchar(2)), 2) AS CHAR(8)) as [DateKey]
,@LoopDate as [FullDateKey],DATEPART(DW, @LoopDate) as DayNumberOfWeek,Datename(dw, @LoopDate) as DayNameOfWeek
,(CASE WHEN DATEPART(DW, @LoopDate)= 1 Then ‘Minggu’
WHEN DATEPART(DW, @LoopDate)= 2 Then ‘Senin’
WHEN DATEPART(DW, @LoopDate)= 3 Then ‘Selasa’
WHEN DATEPART(DW, @LoopDate)= 4 Then ‘Rabu’
WHEN DATEPART(DW, @LoopDate)= 5 Then ‘Kamis’
WHEN DATEPART(DW, @LoopDate)= 6 Then ‘Jum”at’
WHEN DATEPART(DW, @LoopDate)= 7 Then ‘Sabtu’ END) AS INA_DayNameOfWeek
,Datepart(D, @LoopDate) as DayNumberOfMonth,Datepart(Dy, @LoopDate) as DayNumberOfYear
,Datename(ww, @LoopDate) as WeekNumberOfYear
,Datename(Month, @LoopDate) as EnglishMonthName
,(CASE WHEN Month(@LoopDate)= 1 Then ‘Januari’
WHEN Month(@LoopDate)= 2 Then ‘Februari’
WHEN Month(@LoopDate)= 3 Then ‘Maret’
WHEN Month(@LoopDate)= 4 Then ‘April’
WHEN Month(@LoopDate)= 5 Then ‘Mei’
WHEN Month(@LoopDate)= 6 Then ‘Juni’
WHEN Month(@LoopDate)= 7 Then ‘Juli’
WHEN Month(@LoopDate)= 8 Then ‘Agustus’
WHEN Month(@LoopDate)= 9 Then ‘September’
WHEN Month(@LoopDate)= 10 Then ‘Oktober’
WHEN Month(@LoopDate)= 11 Then ‘November’
WHEN Month(@LoopDate)= 12 Then ‘Desember’ END) AS INAMonthName
,Datepart(Month, @LoopDate) as MonthNumberOfYear
,(Case When Month(@LoopDate) Between 1 and 3 Then 1
When Month(@LoopDate) Between 4 and 6 Then 2
When Month(@LoopDate) Between 7 and 9 Then 3
ELSE 4 END) as CalendarQuarter
,Datepart(Year, @LoopDate) as CalendarYear,(Case When Month(@LoopDate) Between 1 and 6 Then 1 Else 2 END) as CalendarSemester
,(Case When Month(@FiscalDate) Between 1 and 3 Then 1
When Month(@FiscalDate) Between 4 and 6 Then 2
When Month(@FiscalDate) Between 7 and 9 Then 3
ELSE 4 END) as FiscalQuarter
,Datepart(Year, @FiscalDate) as FiscalYear,(Case When Month(@FiscalDate) Between 1 and 6 Then 1 Else 2 END) as FiscalSemester

IF @LoopDate>@EndDate
Break
ELSE
SET @Loopdate=DATEADD(DAY, 1, @Loopdate)
Continue
END
Select * From @DimDates
END;
GO

— =============================================
— Author: Aries Budi Susilo
— Create date: 9 Juli 2012
— Description: Script for populated DimDates Table
— =============================================

–Truncate Table DimDates
Truncate Table DimDates

–Insert New Row into DimDates
Insert DimDates
Exec usp_CreateDimDates ‘20010101’, ‘20121231’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s