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’
Think Big 