Table Function For Data Warehouse Time Dimension

,

This script creates a table based function that returns time dimension that would typically be seen in a data warehouse. The time dimension has aggregate values for week, month, quarter, half, and year.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER FUNCTION fn_TimeDimension(@StartDate datetime, @EndDate datetime)
RETURNS @TimeDimension TABLE
	(
	[TimeKey] integer NOT NULL,
	[TheDate] datetime NOT NULL,
	[ISODate] char(8) NOT NULL,

	[WeekName] varchar(7) NOT NULL,
	[WeekNameWithYear] varchar(13) NOT NULL,
	[WeekShortName] char(4) NOT NULL,
	[WeekShortNameWithYear] char(9) NOT NULL,
	[WeekNumber] tinyint NOT NULL,
	[FirstDateOfWeek] datetime NOT NULL,
	[LastDateOfWeek] datetime NOT NULL,
	[DayOfWeek] tinyint NOT NULL,
	[DayOfWeekName] varchar(9) NOT NULL,
	[IsWeekday] bit NOT NULL,
	[IsWeekend] bit NOT NULL,

	[MonthName] varchar(9) NOT NULL,
	[MonthNameWithYear] varchar(15) NOT NULL,
	[MonthShortName] varchar(3) NOT NULL,
	[MonthShortNameWithYear] char(8) NOT NULL,
	[MonthNumber] tinyint NOT NULL,
	[FirstDateOfMonth] datetime NOT NULL,
	[LastDateOfMonth] datetime NOT NULL,
	[DayOfMonth] tinyint NOT NULL,
	[DayOfMonthName] varchar(16) NOT NULL,

	[QuarterName] char(9) NOT NULL,
	[QuarterNameWithYear] char(15) NOT NULL,
	[QuarterShortName] char(2) NOT NULL,
	[QuarterShortNameWithYear] char(7) NOT NULL,
	[QuarterNumber] tinyint NOT NULL,
	[FirstDateOfQuarter] datetime NOT NULL,
	[LastDateOfQuarter] datetime NOT NULL,
	[DayOfQuarter] tinyint NOT NULL,
	[DayOfQuarterName] varchar(16) NOT NULL,

	[HalfName] char(6) NOT NULL,
	[HalfNameWithYear] char(12) NOT NULL,
	[HalfShortName] char(2) NOT NULL,
	[HalfShortNameWithYear] char(7) NOT NULL,
	[HalfNumber] tinyint NOT NULL,
	[FirstDateOfHalf] datetime NOT NULL,
	[LastDateOfHalf] datetime NOT NULL,
	[DayOfHalf] tinyint NOT NULL,
	[DayOfHalfName] varchar(16) NOT NULL,

	[YearName] char(4) NOT NULL,
	[YearShortName] char(2) NOT NULL,
	[YearNumber] smallint NOT NULL,
	[FirstDateOfYear] datetime NOT NULL,
	[LastDateOfYear] datetime NOT NULL,
	[DayOfYear] smallint NOT NULL,
	[DayOfYearName] varchar(20) NOT NULL
	)
AS
BEGIN

DECLARE @TimeKey Int
DECLARE @TheDate DateTime
DECLARE @ISODate char(8)

DECLARE	@WeekName varchar(7)
DECLARE	@WeekNameWithYear varchar(13)
DECLARE	@WeekShortName char(4)
DECLARE	@WeekShortNameWithYear char(9)
DECLARE	@WeekNumber tinyint
DECLARE @FirstDateOfWeek as datetime
DECLARE @LastDateOfWeek as datetime
DECLARE	@DayOfWeek tinyint
DECLARE	@DayOfWeekName varchar(9)
DECLARE @IsWeekday bit
DECLARE @IsWeekend bit

DECLARE	@MonthName varchar(9)
DECLARE	@MonthNameWithYear varchar(15)
DECLARE	@MonthShortName varchar(3)
DECLARE	@MonthShortNameWithYear char(8)
DECLARE	@MonthNumber tinyint
DECLARE @FirstDateOfMonth as datetime
DECLARE @LastDateOfMonth as datetime
DECLARE @DayOfMonth tinyint
DECLARE @DayOfMonthName varchar(16)

DECLARE	@QuarterName char(9)
DECLARE	@QuarterNameWithYear char(15)
DECLARE	@QuarterShortName char(2)
DECLARE	@QuarterShortNameWithYear char(7)
DECLARE	@QuarterNumber tinyint
DECLARE @FirstDateOfQuarter as datetime
DECLARE @LastDateOfQuarter as datetime
DECLARE @DayOfQuarter tinyint
DECLARE @DayOfQuarterName varchar(20)

DECLARE	@HalfName char(6)
DECLARE	@HalfNameWithYear char(12)
DECLARE	@HalfShortName char(2)
DECLARE	@HalfShortNameWithYear char(7)
DECLARE	@HalfNumber tinyint
DECLARE @FirstDateOfHalf as datetime
DECLARE @LastDateOfHalf as datetime
DECLARE @DayOfHalf tinyint
DECLARE @DayOfHalfName varchar(20)

DECLARE	@YearName char(4)
DECLARE	@YearShortName char(2)
DECLARE	@YearNumber smallint
DECLARE @FirstDateOfYear as datetime
DECLARE @LastDateOfYear as datetime
DECLARE @DayOfYear smallint
DECLARE @DayOfYearName varchar(20)


SET @TheDate = @StartDate

WHILE DateDiff(day,@TheDate,@EndDate) >= 0
BEGIN

	SET @TimeKey = DateDiff(day,@StartDate,@EndDate) - DateDiff(day,@TheDate,@EndDate) + 1
	SET @ISODate = convert(char(8),@TheDate,112)

	SET @YearNumber = DATEPART(yy,@TheDate)
	SET @YearName = right('0000' + cast(@YearNumber as varchar(4)),4)
	SET @YearShortName = right('0000' + cast(@YearNumber as varchar(4)),2)
	SET @FirstDateOfYear = cast(@YearName + '-01-01' as datetime)
	SET @LastDateOfYear = cast(@YearName + '-12-31' as datetime)
	SET @DayOfYear = DATEPART(dy, @TheDate)

	SET @QuarterNumber = DATEPART(q, @TheDate)
	SET @QuarterName = 'Quarter ' + cast(@QuarterNumber as char(1))
	SET @QuarterNameWithYear = @QuarterName + ', ' + @YearName
	SET @QuarterShortName = 'Q' + cast(@QuarterNumber as char(1))
	SET @QuarterShortNameWithYear = @QuarterShortName + ' ' + @YearName
	SET @FirstDateOfQuarter = 
		CASE @QuarterNumber
		WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
		WHEN 2 THEN cast(@YearName + '-04-01' as datetime)
		WHEN 3 THEN cast(@YearName + '-07-01' as datetime)
		WHEN 4 THEN cast(@YearName + '-10-01' as datetime)
		END
	SET @LastDateOfQuarter = dateadd(day,-1,dateadd(q,1,@FirstDateOfQuarter))
	SET @DayOfQuarter = DateDiff(day,@FirstDateOfQuarter, @TheDate) + 1
	SET @DayOfQuarterName = 'Day ' + cast(DateDiff(day,@FirstDateOfQuarter, @TheDate) + 1 as varchar(2)) + ' of Q' + cast(@QuarterNumber as char(1))

	SET @HalfNumber = CASE WHEN DATEPART(q, @TheDate) <= 2 THEN 1 ELSE 2 END
	SET @HalfName = 'Half ' + cast(@HalfNumber as char(1))
	SET @HalfNameWithYear = @HalfName + ', ' + @YearName
	SET @HalfShortName = 'H' + cast(@HalfNumber as char(1))
	SET @HalfShortNameWithYear = @HalfShortName + ' ' + @YearName
	SET @FirstDateOfHalf = 
		CASE @HalfNumber
		WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
		WHEN 2 THEN cast(@YearName + '-07-01' as datetime)
		END
	SET @LastDateOfHalf = 
		CASE @HalfNumber
		WHEN 1 THEN cast(@YearName + '-06-30' as datetime)
		WHEN 2 THEN cast(@YearName + '-12-31' as datetime)
		END
	SET @DayOfHalf = DateDiff(day,@FirstDateOfHalf, @TheDate) + 1
	SET @DayOfHalfName = 'Day ' + cast(DateDiff(day,@FirstDateOfHalf, @TheDate) + 1 as varchar(3)) + ' of H' + cast(@HalfNumber as char(1))

	SET @MonthName = DATENAME(mm, @TheDate)
	SET @MonthNameWithYear = @MonthName + ', ' + @YearName
	SET @MonthShortName = DATENAME(m, @TheDate)
	SET @MonthShortNameWithYear = @MonthShortName + ' ' + @YearName
	SET @MonthNumber = DATEPART(m, @TheDate)
	SET @FirstDateOfMonth = cast(@YearName + '-'+@MonthShortName+'-01' as datetime)
	SET @LastDateOfMonth = dateadd(day,-1,dateadd(m,1,@FirstDateOfMonth))
	SET @DayOfMonth = DATEPART(d, @TheDate)
	SET @DayOfMonthName = datename(m,@TheDate) + ' ' + 
		cast(datepart(d,@TheDate) as varchar(2)) +
		CASE left(right('00' + cast(datepart(d,@TheDate) as varchar(2)),2),1)
		WHEN '1' THEN
			'th'
		ELSE 
			CASE right(right('00' + cast(datepart(d,@TheDate) as varchar(2)),2),1)
			WHEN '1' THEN 'st'
			WHEN '2' THEN 'nd'
			WHEN '3' THEN 'rd'
			ELSE 'th'
			END
		END
	SET @DayOfYearName = @DayOfMonthName + ', ' + @YearName

	SET @WeekName = 'Week ' + datename(wk,@TheDate)
	SET @WeekNameWithYear =@WeekName + ', ' + @YearName
	SET @WeekShortName = 'WK'+right('00'+datename(wk,@TheDate),2)
	SET @WeekShortNameWithYear = @WeekShortName + ' ' + @YearName
	SET @WeekNumber = datepart(wk,@TheDate)

	SET @FirstDateOfWeek = dateadd(day,(datepart(dw,@TheDate)-1)*-1,@TheDate)
	SET @LastDateOfWeek = dateadd(day,-1,dateadd(wk,1,@FirstDateOfWeek))
	SET @DayOfWeek = DATEPART(dw, @TheDate)
	SET @DayOfWeekName = DATENAME(dw, @TheDate)
	SET @IsWeekday = CASE @DayOfWeek WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END
	SET @IsWeekend = CASE @DayOfWeek WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END

	INSERT INTO @TimeDimension
	(
		[TimeKey],
		[TheDate],
		[ISODate],

		[WeekName],
		[WeekNameWithYear],
		[WeekShortName],
		[WeekShortNameWithYear],
		[WeekNumber],
		[FirstDateOfWeek],
		[LastDateOfWeek],
		[DayOfWeek],
		[DayOfWeekName],
		[IsWeekday],
		[IsWeekend],

		[MonthName],
		[MonthNameWithYear],
		[MonthShortName],
		[MonthShortNameWithYear],
		[MonthNumber],
		[FirstDateOfMonth],
		[LastDateOfMonth],
		[DayOfMonth],
		[DayOfMonthName],

		[QuarterName],
		[QuarterNameWithYear],
		[QuarterShortName],
		[QuarterShortNameWithYear],
		[QuarterNumber],
		[FirstDateOfQuarter],
		[LastDateOfQuarter],
		[DayOfQuarter],
		[DayOfQuarterName],

		[HalfName],
		[HalfNameWithYear],
		[HalfShortName],
		[HalfShortNameWithYear],
		[HalfNumber],
		[FirstDateOfHalf],
		[LastDateOfHalf],
		[DayOfHalf],
		[DayOfHalfName],

		[YearName],
		[YearShortName],
		[YearNumber],
		[FirstDateOfYear],
		[LastDateOfYear],
		[DayOfYear],
		[DayOfYearName]
	)
	VALUES
	(
		@TimeKey,
		@TheDate,
		@ISODate,

		@WeekName,
		@WeekNameWithYear,
		@WeekShortName,
		@WeekShortNameWithYear,
		@WeekNumber,
		@FirstDateOfWeek,
		@LastDateOfWeek,
		@DayOfWeek,
		@DayOfWeekName,
		@IsWeekday,
		@IsWeekend,


		@MonthName,
		@MonthNameWithYear,
		@MonthShortName,
		@MonthShortNameWithYear,
		@MonthNumber,
		@FirstDateOfMonth,
		@LastDateOfMonth,
		@DayOfMonth,
		@DayOfMonthName,

		@QuarterName,
		@QuarterNameWithYear,
		@QuarterShortName,
		@QuarterShortNameWithYear,
		@QuarterNumber,
		@FirstDateOfQuarter,
		@LastDateOfQuarter,
		@DayOfQuarter,
		@DayOfQuarterName,

		@HalfName,
		@HalfNameWithYear,
		@HalfShortName,
		@HalfShortNameWithYear,
		@HalfNumber,
		@FirstDateOfHalf,
		@LastDateOfHalf,
		@DayOfHalf,
		@DayOfHalfName,

		@YearName,
		@YearShortName,
		@YearNumber,
		@FirstDateOfYear,
		@LastDateOfYear,
		@DayOfYear,
		@DayOfYearName
	)

	SET @TheDate = DATEADD(day, 1, @TheDate)
END
RETURN
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/*
--------------------------------------------------------
-- Make Use Of The Table Function Like This
--------------------------------------------------------
select *
from fn_TimeDimension('01/01/1999', '01/01/2004')
*/

Rate

5 (1)

Share

Share

Rate

5 (1)