Time Dimension creation script

DDL for a time dimension table.

Create table DimTime
(
	time_key smallint NOT NULL,
	time_type varchar(20) NOT NULL,
	full_time time(0) NULL,
	hour_of_day tinyint NULL,
	minute_of_hour tinyint NULL,
	hourly_segments varchar(13) NULL,
	minute_15_segment_key tinyint NULL,
	minute_15_segments varchar(13) NULL,
	minute_30_segment_key tinyint NULL,
	minute_30_segments varchar(13) NULL,
	 CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED 	 (time_key)
)
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Time dimension table with grain = 1 munute.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = NULL,
    @level2name = NULL
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Minute of day (0 - 1439).  Special values (e.g. UNKNOWN) are negative. Primary Key.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'time_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Normal = minute of day; UNKNOWN, NOT SPECIFIED, INVALID)',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'time_type'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Full time of day: 00:00 to 23:59.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'full_time'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Hour of day: 0 to 23.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'hour_of_day'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Minute of hour: 1 to 59.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_of_hour'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for hourly segment. e.g. "10:00-10:59".',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'hourly_segments'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Key (and sort order) for each 15-minute segment of day: 0 to 95.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_15_segment_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for 15-minute segment. e.g. "09:15-09:29".',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_15_segments'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Key (and sort order) for each 30-minute segment of day: 0 - 47.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_30_segment_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for 30-minute segment. e.g. "11:30-11:59"',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_30_segments'

 Table Load script

Populates table with minutes for one day.  Includes special-meaning rows (-1 to -3)

-- Populate DimTime table.
-- Special Values
INSERT INTO DimTime (time_key, time_type)
	VALUES
		(-3, 'INVALID'),
		(-2, 'UNASSIGNED'),
		(-1, 'UNKNOWN')
-- minutes 0 - 1439
DECLARE @i int = 0
WHILE (@i < 1440)
BEGIN
	INSERT INTO dbo.DimTime
		VALUES
			(@i
			,'Normal'
			,DATEADD(minute, @i, '00:00')
			,DATEPART(hour, DATEADD(minute, @i, '00:00'))
			,DATEPART(minute, DATEADD(minute, @i, '00:00'))
			,CAST(CAST(DATEADD(minute, @i, '00:00') AS time) AS char(2)) + ':00-' +
				CAST(CAST(DATEADD(minute, @i, '00:00') AS time) AS char(2)) + ':59'
			,@i / 15
			,NULL
			,@i / 30
			,NULL
			)
	SET @i += 1
END

-- 15 minute buckets
UPDATE DimTime SET minute_15_segments =
	CAST(CAST(DATEADD(minute, (minute_15_segment_key * 15), '00:00') AS time) AS char(5)) + '-' +
		CAST(CAST(DATEADD(minute, 14 + (minute_15_segment_key * 15), '00:00') AS time) AS char(5)) 
-- 30 minute buckets
UPDATE DimTime SET minute_30_segments =
	CAST(CAST(DATEADD(minute, (minute_30_segment_key * 30), '00:00') AS time) AS char(5)) + '-' +
		CAST(CAST(DATEADD(minute, 29 + (minute_30_segment_key * 30), '00:00') AS time) AS char(5)) 

Add time_key to Existing Table

The key for DimTime, time_key, is meaningful, being the minute of the day (0 to 1439).   That makes a computed column acceptable when joining the date dimension to existing tables.

-- Add a computed time_key column.
ALTER TABLE  [table-name]
ADD time_key AS CAST(DATEDIFF(minute, CAST(n_DateStamp AS DATE), n_DateStamp) AS smallint)