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:
- To Create DimTime Table execute Script 1
- 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.
- 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);
