Technical Article

Create and Populate a Date Dimension Table

,

Add statements at the top to select your database:

USE [DATABASE]
GO

Save and run the script in the query analyzer. I have used this in SQL Server 2008, 2008R2 and 2012. I have no reason to believe it won't work in 2005 or even 2000, but I have not tested it in those environments.

Running on a Windows 7 Developer box against a 2008 R2 server it took about 43 sections to generate a 25-year table.

/*
This script is freely shared by the author, Joseph M. Morgan, Principal Programmer/Analyst I at Amerigroup, a division of Wellpoint. Permission is granted to copy, share, modify and distribute it without restriction except for commercial gain, in which case send me some of the money!
*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


SET NOCOUNT ON;

IF NOT EXISTS ( SELECT
                        *
                    FROM
                        sys.objects
                    WHERE
                        object_id = OBJECT_ID(N'[dbo].[DATE_DIM]')
                        AND type IN (N'U') ) 
   BEGIN
                
              
         CREATE TABLE [dbo].[DATE_DIM]
                (
                 [DATE_ID] [bigint] IDENTITY(1, 1)
                                    NOT NULL
                ,[SQL_DATE] [datetime] NOT NULL
                ,[DAY] [smallint] NOT NULL
                ,[DAY_OF_WEEK] [smallint] NULL
                ,[WEEK] [smallint] NOT NULL
                ,[MONTH] [SMALLINT] NOT NULL
                ,[QUARTER] [SMALLINT] NOT NULL
                ,[YEAR] [SMALLINT] NOT NULL
                ,[DAY_OF_YEAR] [SMALLINT] NOT NULL
                ,[DAY_TEXT] [VARCHAR](50) NOT NULL
                ,[MONTH_TEXT] [VARCHAR](50) NOT NULL
                ,[QUARTER_TEXT] [VARCHAR](50) NOT NULL
                ,[DAY_TEXT_ABBREV] [VARCHAR](3) NULL
                ,[MONTH_TEXT_ABBREV] [VARCHAR](3) NULL
                ,[LDM] [BIGINT] NULL
                ,[LDQ] [BIGINT] NULL
                ,[LDY] [BIGINT] NULL
                ,[IS_BUS_DAY] [BIT] NULL
                ,[IS_WEEKDAY] [BIT] NULL
                ,[IS_HOLIDAY] [BIT] NULL
                ,[RPT_HDR_LONG] AS CONVERT(VARCHAR(25), (([MONTH_TEXT] + ' ')
                                                         + CONVERT([VARCHAR](4), [YEAR], (0))))
                    PERSISTED
                ,[RPT_HDR_SHORT] AS CONVERT(VARCHAR(25), (([MONTH_TEXT_ABBREV]
                                                           + ' ')
                                                          + CONVERT([VARCHAR](4), [YEAR], (0))))
                    PERSISTED
                ,CONSTRAINT [PK_Date_dbo] PRIMARY KEY CLUSTERED
                    ([DATE_ID] ASC)
                )
         CREATE NONCLUSTERED INDEX [IX_Dates] ON [dbo].[DATE_DIM]
         (
         [SQL_DATE] ASC
         )
         CREATE NONCLUSTERED INDEX [IX_Dates_1] ON [dbo].[DATE_DIM]
         (
         [Year] ASC
         )
         CREATE NONCLUSTERED INDEX [IX_Dates_2] ON [dbo].[DATE_DIM]
         (
         [Month] ASC
         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)


/****** Object:  Index [IX_Dates_3]    Script Date: 11/30/2012 10:20:53 AM ******/         CREATE NONCLUSTERED INDEX [IX_Dates_3] ON [dbo].[DATE_DIM]
         (
         [Day] ASC
         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)


/****** Object:  Index [IX_Dates_4]    Script Date: 11/30/2012 10:20:53 AM ******/         CREATE NONCLUSTERED INDEX [IX_Dates_4] ON [dbo].[DATE_DIM]
         (
         [LDM] ASC
         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
   END
ELSE 
   BEGIN       
  -- If it already existed, it might have data in it, so we truncate just in case.   
         TRUNCATE TABLE dbo.DATE_DIM
   END
  GO 
/* ====================== Create the three functions used in the code. ======================
(We avoided errors by creating the table the functions reference first.)*/    

IF OBJECT_ID(N'dbo.svfn_AMS_LDM') IS NOT NULL 
   DROP FUNCTION dbo.svfn_AMS_LDM
GO

CREATE FUNCTION [dbo].[svfn_AMS_LDM]
       (
        @MONTH SMALLINT
       ,@YEAR SMALLINT
       )
RETURNS BIGINT
AS 
    BEGIN
        DECLARE @RESULT BIGINT
        SET @RESULT = (SELECT
                            MAX(DATE_ID)
                        FROM
                            DBO.DATE_DIM
                        WHERE
                            [MONTH] = @MONTH
                            AND [YEAR] = @YEAR
                      )

        RETURN @RESULT
    END


GO

IF OBJECT_ID(N'dbo.svfn_AMS_LDQ') IS NOT NULL 
   DROP FUNCTION dbo.svfn_AMS_LDQ
GO

CREATE FUNCTION [dbo].[svfn_AMS_LDQ]
       (
        @QUARTER SMALLINT
       ,@YEAR SMALLINT
       )
RETURNS BIGINT
AS 
    BEGIN
        DECLARE @RESULT BIGINT
        SET @RESULT = (SELECT
                            MAX(DATE_ID)
                        FROM
                            DATE_DIM
                        WHERE
                            [QUARTER] = @QUARTER
                            AND [YEAR] = @YEAR
                      )

        RETURN @RESULT
    END
GO

IF OBJECT_ID(N'dbo.svfn_AMS_LDY') IS NOT NULL 
   DROP FUNCTION dbo.svfn_AMS_LDY
GO

CREATE FUNCTION [dbo].[svfn_AMS_LDY] (@YEAR SMALLINT)
RETURNS BIGINT
AS 
    BEGIN
        DECLARE @RESULT BIGINT
        SET @RESULT = (SELECT
                            MAX(DATE_ID)
                        FROM
                            DATE_DIM
                        WHERE
                            [YEAR] = @YEAR
                      )

        RETURN @RESULT
    END

GO

/*====================== Now populate the table =============================== */     
BEGIN TRY
-- Declare and set the date, which is the starting date that will be incremented, and the end date desired
     
      DECLARE @Date DATETIME
      DECLARE @EndDate DATETIME
      SET @Date = CONVERT(DATETIME, '01-01-2000')
      SET @EndDate = CONVERT(DATETIME, '12-31-2025')
      WHILE @Date <= @EndDate 
            BEGIN
                  INSERT INTO [dbo].[DATE_DIM]
                        (SQL_DATE
                        ,[DAY]
                        ,[DAY_OF_WEEK]
                        ,[WEEK]
                        ,[MONTH]
                        ,[QUARTER]
                        ,[YEAR]
                        ,[DAY_OF_YEAR]
                        ,[DAY_TEXT]
                        ,[MONTH_TEXT]
                        ,[QUARTER_TEXT]
                        ,[DAY_TEXT_ABBREV]
                        ,[MONTH_TEXT_ABBREV]
                        ,[IS_BUS_DAY]
                        ,[IS_WEEKDAY]
                        ,[IS_HOLIDAY]
                                
                        )
                        SELECT
                                @DATE AS SQLDATE
                               ,DATEPART(D, @DATE) AS [DAY]
                               ,DATEPART(DW, @DATE) AS [DAY_OF_WEEK]
                               ,DATEPART(WK, @DATE) AS [WEEK]
                               ,DATEPART(M, @DATE) AS [MONTH]
                               ,DATEPART(Q, @DATE) AS [QUARTER]
                               ,DATEPART(YYYY, @DATE) AS [YEAR]
                               ,DATEPART(DY, @DATE) AS [DAY_OF_YEAR]
                               ,DATENAME(DW, @DATE) AS DAY_TEXT
                               ,DATENAME(M, @DATE) AS MONTH_TEXT
                               ,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT
                               ,SUBSTRING(DATENAME(DW, @DATE), 1, 3) AS DAY_TEXTABBREVIATION
                               ,SUBSTRING(DATENAME(M, @DATE), 1, 3) AS MONTH_TEXTABBREVIATION
                               ,IS_BUS_DAY = CASE DATEPART(DW, @DATE)
                                               WHEN 7 THEN 0
                                               WHEN 1 THEN 0
                                               ELSE 1
                                             END
                               ,ISWEEKDAY = CASE DATEPART(DW, @DATE)
                                              WHEN 7 THEN 0
                                              WHEN 1 THEN 0
                                              ELSE 1
                                            END
                               ,0 AS IS_HOLIDAY
                  SET @DATE = DATEADD(D, 1, @DATE)
            END
/* ======================  Add holidays to the table ========================

 First set the default U.S. Federal Holidays to be holidays, not business days. 
 In some cases the dates are fixed, in some they are changeable. If your company treats
 any of these as business days, just comment out the relevant section(s) */
      UPDATE
            [dbo].[DATE_DIM]
        SET 
            IS_BUS_DAY = 0
           ,IS_HOLIDAY = 1
        WHERE
            (
--New [Year]'s Day (Jan 1)
             [Month] = 1
             AND DAY = 1
             OR
--Christmas Day (Dec 25)
             [Month] = 12
             AND DAY = 25
             OR
--Independence Day (Jul 4)
             [Month] = 7
             AND DAY = 4
             OR 
--Veteran's Day (Nov 11)
             [Month] = 11
             AND Day = 11
             OR
--Thanksgiving (4th Thursday)
             ([Month] = 11
              AND [DAY_OF_WEEK] = 5
              AND DATE_ID IN (SELECT
                                    MIN(DATE_ID) + 21
                                FROM
                                    DATE_DIM
                                WHERE
                                    [Month] = 11
                                    AND [DAY_OF_WEEK] = 5
                                GROUP BY
                                    [Year])
             )
             OR
--Memorial Day (last Monday)
             ([Month] = 5
              AND [DAY_OF_WEEK] = 2
              AND DATE_ID IN (SELECT
                                    MAX(DATE_ID)
                                FROM
                                    DATE_DIM
                                WHERE
                                    [Month] = 5
                                    AND [DAY_OF_WEEK] = 2
                                GROUP BY
                                    [Year])
             )
             OR
-- Labor Day (1st Monday)
             ([Month] = 9
              AND [DAY_OF_WEEK] = 2
              AND DATE_ID IN (SELECT
                                    MIN(DATE_ID)
                                FROM
                                    DATE_DIM
                                WHERE
                                    [Month] = 9
                                    AND [DAY_OF_WEEK] = 2
                                GROUP BY
                                    [Year])
             )
             OR
--Martin Luther King Day (3rd Monday)
             ([Month] = 1
              AND [DAY_OF_WEEK] = 2
              AND DATE_ID IN (SELECT
                                    MIN(DATE_ID) + 14
                                FROM
                                    DATE_DIM
                                WHERE
                                    [Month] = 1
                                    AND [DAY_OF_WEEK] = 2
                                GROUP BY
                                    [Year])
             )
-- Columbus Day
             OR ([Month] = 10
                 AND [DAY_OF_WEEK] = 2
                 AND DATE_ID IN (SELECT
                                        MIN(DATE_ID) + 7
                                    FROM
                                        DATE_DIM
                                    WHERE
                                        [Month] = 10
                                        AND [DAY_OF_WEEK] = 2
                                    GROUP BY
                                        [Year])
                )
            )

/* ====== Now adjust for the days when the holiday is not a "Monday Holiday", and falls on a weekend.  ======
In most companies, Saturday holidays are observed the Friday before and Sunday holidays the Monday after, 
but you can adjust this as needed */
--Set Saturday holidays to Friday
      UPDATE
            [dbo].[DATE_DIM]
        SET 
            IS_HOLIDAY = 1
           ,IS_BUS_DAY = 0
        WHERE
            DATE_ID IN (SELECT
                                DATE_ID - 1
                            FROM
                                DATE_DIM
                            WHERE
                                IS_HOLIDAY = 1
                                AND [DAY_OF_WEEK] = 7)

--Set Sunday holidays to Monday
      UPDATE
            [dbo].[DATE_DIM]
        SET 
            IS_HOLIDAY = 1
           ,IS_BUS_DAY = 0
        WHERE
            DATE_ID IN (SELECT
                                DATE_ID + 1
                            FROM
                                DATE_DIM
                            WHERE
                                IS_HOLIDAY = 1
                                AND [DAY_OF_WEEK] = 1)

/* ========================== Add in the end-of-period values ==================== 
These are especially useful for aggregation and sorting, since you can order by LDM,
for example, and have the order be in the correct calendar order, even across multiple years instead of running datepart calculations for this purpose
*/      UPDATE
            [dbo].[DATE_DIM]
        SET 
            LDM = ld.LDM
           ,LDQ = ld.LDQ
           ,LDY = ld.LDY
        FROM
            (SELECT
                    DATE_ID
                   ,dbo.svfn_AMS_LDM(Month, Year) AS LDM
                   ,dbo.svfn_AMS_LDQ(Quarter, Year) AS LDQ
                   ,dbo.svfn_AMS_LDY(Year) AS LDY
                   ,dates_1.MONTH_TEXT
                   ,dates_1.[MONTH_TEXT_ABBREV]
                   ,dates_1.Year
                FROM
                    [dbo].[DATE_DIM] AS Dates_1
            ) AS ld
        INNER JOIN [dbo].[DATE_DIM]
            ON ld.DATE_ID = [dbo].[DATE_DIM].DATE_ID

      
      SELECT
            RESULT = 'Success'
END TRY
BEGIN CATCH

      SELECT
            RESULT = 'Error ' + CONVERT(VARCHAR(2000), @@ERROR) + ' on line '
            + ERROR_LINE() + ': ' + ERROR_MESSAGE()
      RETURN       
END CATCH                   

GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating