Date Dimensions in T-SQL using CTE

,

As several blogs/links are available for getting Time Dimension Scripts e.g.:

  • http://www.sqlserver007.com/2010/01/20/sql-server-date-and-time-dimensions-in-t-sql-using-cte/
  • http://consultingblogs.emc.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx
  • http://www.dataprix.com/en/blogs/carlos/time-dimension-structure-and-load-script-sqlserver
  • http://www.sqlservercentral.com/scripts/Data+Warehousing/65762/

I required new Time Dimension script as I required little more information for Semister, Quater, Month,ForthNight, Week etc. Folloings are steps to create & populate data into Time Dimension table:

  1. To Create DimTime Table execute Script 1
  2. By Default it will populate data between 01-01-2010 and 31-12-2020 dates. If anybody wants to change date range please set date values for parameters @StartDate & @EndDate.
  3. To Populate Data into DimTime table execute Script 2

 

--> Script 1: Creating DimTime table structure.
CREATE TABLE [dbo].[DimTime](
	[TimeKey] [int] NOT NULL,
	[TimeFullDate] [date] NOT NULL,
	[TimeDayNumberOfWeek] [tinyint] NOT NULL,
	[TimeEnglishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[TimeDayNumberOfForthNight] [tinyint] NOT NULL,
	[TimeDayNumberOfMonth] [tinyint] NOT NULL,
	[TimeDayNumberOfQuarter] [tinyint] NOT NULL,
	[TimeDayNumberOfSemester] [tinyint] NOT NULL,
	[TimeDayNumberOfYear] [smallint] NOT NULL,
	[TimeWeekNumberOfMonth] [tinyint] NOT NULL,
	[TimeWeekNumberOfQuarter] [tinyint] NOT NULL,
	[TimeWeekNumberOfSemester] [tinyint] NOT NULL,
	[TimeWeekNumberOfYear] [tinyint] NOT NULL,
	[TimeForthNightNumberOfMonth] [tinyint] NOT NULL,
	[TimeForthNightNumberOfQuarter] [tinyint] NOT NULL,
	[TimeForthNightNumberOfSemester] [tinyint] NOT NULL,
	[TimeForthNightNumberOfYear] [tinyint] NOT NULL,
	[TimeMonthNumberOfQuarter] [tinyint] NOT NULL,
	[TimeMonthNumberOfSemester] [tinyint] NOT NULL,
	[TimeMonthNumberOfYear] [tinyint] NOT NULL,
	[TimeEnglishMonthName] [nvarchar](10) NOT NULL,
	[TimeQuarterNumberOfSemester] [tinyint] NOT NULL,
	[TimeQuarterNumberOfYear] [tinyint] NOT NULL,
	[TimeSemesterNumberOfYear] [tinyint] NOT NULL,
	[TimeYear] [int] NOT NULL,
	CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED ([TimeKey] ASC)
) ON [PRIMARY]
GO
--> Script 2: Polulating Data into DimTime Table.
SET DATEFORMAT DMY;
DECLARE 
    @StartDate DATETIME = '01-01-2010',
    @EndDate DATETIME = '31-12-2020';

WITH DateCTE AS
(
    SELECT 
        TimeKey = CONVERT(INT,CONVERT(NVARCHAR(10),@StartDate,112)),
        FullDate = @StartDate
    UNION ALL
    SELECT 
        TimeKey = CONVERT(INT,CONVERT(NVARCHAR(10),FullDate + 1,112)),
        FullDate = FullDate + 1
    FROM DateCTE 
    WHERE FullDate + 1 < = @EndDate
),
TimeCTE AS
(
SELECT 
    TimeKey,
    TimeFullDate = CONVERT(DATE,FullDate),
    TimeDayNumberOfWeek = DATEPART(DW,FullDate),
    TimeEnglishDayNameOfWeek = DATENAME(WEEKDAY,FullDate),
    TimeDayNumberOfForthNight = 
        ROW_NUMBER() 
        OVER(PARTITION BY 
        DATENAME(YEAR,FullDate),
        DATEPART(MM,FullDate),
        (CASE 
            WHEN DATEPART(DD,FullDate) <= 15 THEN 1
            ELSE 2
        END)
        ORDER BY TimeKey),
    TimeDayNumberOfMonth = DATEPART(DD,FullDate),
    TimeDayNumberOfQuarter = 
        ROW_NUMBER() 
        OVER(PARTITION BY 
        DATENAME(YEAR,FullDate),
        (CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3) THEN 1
            WHEN DATEPART(MM,FullDate) IN (4,5,6) THEN 2
            WHEN DATEPART(MM,FullDate) IN (7,8,9) THEN 3
            WHEN DATEPART(MM,FullDate) IN (10,11,12) THEN 4
        END)
        ORDER BY TimeKey),
    TimeDayNumberOfSemester = 
        ROW_NUMBER() 
        OVER(PARTITION BY 
        DATENAME(YEAR,FullDate),
        (CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3,4,5,6) THEN 1
            WHEN DATEPART(MM,FullDate) IN (7,8,9,10,11,12) THEN 2
        END)
        ORDER BY TimeKey),
    TimeDayNumberOfYear = DATEPART(DAYOFYEAR,FullDate),
    TimeWeekNumberOfMonth = DENSE_RANK() OVER(PARTITION BY DATEPART(MM,FullDate) ORDER BY DATEPART(WK,FullDate)),
    TimeWeekNumberOfQuarter = 
        DENSE_RANK() 
        OVER(PARTITION BY 
        (CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3) THEN 1
            WHEN DATEPART(MM,FullDate) IN (4,5,6) THEN 2
            WHEN DATEPART(MM,FullDate) IN (7,8,9) THEN 3
            WHEN DATEPART(MM,FullDate) IN (10,11,12) THEN 4
        END)
        ORDER BY DATEPART(WK,FullDate)),    
    TimeWeekNumberOfSemester = 
        DENSE_RANK() 
        OVER(PARTITION BY 
        (CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3,4,5,6) THEN 1
            WHEN DATEPART(MM,FullDate) IN (7,8,9,10,11,12) THEN 2
        END)
        ORDER BY DATEPART(WK,FullDate)),    
    TimeWeekNumberOfYear = DATEPART(WK,FullDate),
    TimeForthNightNumberOfMonth = 
        CASE 
            WHEN DATEPART(DD,FullDate) <= 15 THEN 1
            ELSE 2
        END,
    --TimeForthNightNumberOfQuarter
    --TimeForthNightNumberOfSemester
    --TimeForthNightNumberOfYear
    TimeMonthNumberOfQuarter = 
        CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3) THEN DATEPART(MM,FullDate)
            WHEN DATEPART(MM,FullDate) IN (4,5,6) THEN DATEPART(MM,FullDate)-3
            WHEN DATEPART(MM,FullDate) IN (7,8,9) THEN DATEPART(MM,FullDate)-6
            WHEN DATEPART(MM,FullDate) IN (10,11,12) THEN DATEPART(MM,FullDate)-9
        END,
    TimeMonthNumberOfSemester = 
        CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3,4,5,6) THEN DATEPART(MM,FullDate)
            WHEN DATEPART(MM,FullDate) IN (7,8,9,10,11,12) THEN DATEPART(MM,FullDate)-6
        END,
    TimeMonthNumberOfYear = DATEPART(MM,FullDate),
    TimeEnglishMonthName = DATENAME(MONTH,FullDate),
    TimeQuarterNumberOfSemester = 
        CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3) THEN 1
            WHEN DATEPART(MM,FullDate) IN (4,5,6) THEN 2
            WHEN DATEPART(MM,FullDate) IN (7,8,9) THEN 1
            WHEN DATEPART(MM,FullDate) IN (10,11,12) THEN 2
        END,
    TimeQuarterNumberOfYear = 
        CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3) THEN 1
            WHEN DATEPART(MM,FullDate) IN (4,5,6) THEN 2
            WHEN DATEPART(MM,FullDate) IN (7,8,9) THEN 3
            WHEN DATEPART(MM,FullDate) IN (10,11,12) THEN 4
        END,
    TimeSemesterNumberOfYear = 
        CASE 
            WHEN DATEPART(MM,FullDate) IN (1,2,3,4,5,6) THEN 1
            WHEN DATEPART(MM,FullDate) IN (7,8,9,10,11,12) THEN 2
        END,
    TimeYear = DATENAME(YEAR,FullDate)
FROM DateCTE 
)
INSERT INTO DimTime
SELECT 
    TimeKey,
    TimeFullDate,
    TimeDayNumberOfWeek,
    TimeEnglishDayNameOfWeek,
    TimeDayNumberOfForthNight,
    TimeDayNumberOfMonth,
    TimeDayNumberOfQuarter,
    TimeDayNumberOfSemester,
    TimeDayNumberOfYear,
    TimeWeekNumberOfMonth,
    TimeWeekNumberOfQuarter,
    TimeWeekNumberOfSemester,
    TimeWeekNumberOfYear,
    TimeForthNightNumberOfMonth,
    TimeForthNightNumberOfQuarter = 
        TimeForthNightNumberOfMonth+(2*TimeMonthNumberOfQuarter)-2,
    TimeForthNightNumberOfSemester = 
        TimeForthNightNumberOfMonth+(2*TimeMonthNumberOfSemester)-2,
    TimeForthNightNumberOfYear = 
        TimeForthNightNumberOfMonth+(2*TimeMonthNumberOfYear)-2,
    TimeMonthNumberOfQuarter,
    TimeMonthNumberOfSemester,
    TimeMonthNumberOfYear,
    TimeEnglishMonthName,
    TimeQuarterNumberOfSemester,
    TimeQuarterNumberOfYear,
    TimeSemesterNumberOfYear,
    TimeYear
FROM TimeCTE
ORDER BY 1
OPTION (MAXRECURSION 0);

Rate

3.86 (7)

Share

Share

Rate

3.86 (7)