SQL Server-ing you

A blog about SQL Server

Archive for the ‘Data Warehouse’ Category

T_SQL – Stored Procedure to create a time dimension

leave a comment »

This is a stored procedure that will build a 24 hour time dimension with it’s boundaries.

Note: you should create the final table and plan for a surrogate key with identity(1,1).

Also, to store hours in a 24h style, it’s not possible to use an int data type as this would wipe out the starting 0 (zero).

Anyway. here goes:

ALTER PROCEDURE Sp_Create_Dim_Time
AS

BEGIN

DECLARE @COUNT INT
SET @COUNT = 1

CREATE TABLE Dim_Time_TEMP
(

Dim_Time_Time varchar(2) not null,
Dim_Time_min varchar(8) not null,
Dim_Time_max varchar(8) not null
)

WHILE @COUNT <= 24
BEGIN

INSERT    INTO Dim_Time_TEMP
SELECT    CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))
END [TIME],

CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:00:00’
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:00:00’
END [MIN],

CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:59:59’
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:59:59’
END [MAX]

SET @COUNT = @COUNT + 1

END

INSERT INTO Dim_Time (Dim_Time_Time, Dim_Time_Min, Dim_Time_Max)
SELECT        Dim_Time_Time, Dim_Time_Min, Dim_Time_Max
FROM        Dim_Time_TEMP
ORDER BY    Dim_Time_Time

DROP TABLE Dim_Time_TEMP

END

The result will look like this:

Dim_Time_Sk    Dim_Time_Time    Dim_Time_Min    Dim_Time_Max
1    00    00:00:00    00:59:59
2    01    01:00:00    01:59:59
3    02    02:00:00    02:59:59
4    03    03:00:00    03:59:59
5    04    04:00:00    04:59:59
6    05    05:00:00    05:59:59
7    06    06:00:00    06:59:59
8    07    07:00:00    07:59:59
9    08    08:00:00    08:59:59
10    09    09:00:00    09:59:59
11    10    10:00:00    10:59:59
12    11    11:00:00    11:59:59
13    12    12:00:00    12:59:59
14    13    13:00:00    13:59:59
15    14    14:00:00    14:59:59
16    15    15:00:00    15:59:59
17    16    16:00:00    16:59:59
18    17    17:00:00    17:59:59
19    18    18:00:00    18:59:59
20    19    19:00:00    19:59:59
21    20    20:00:00    20:59:59
22    21    21:00:00    21:59:59
23    22    22:00:00    22:59:59
24    23    23:00:00    23:59:59

Hope you enjoyed

Nico

Advertisements

Written by Nicolas de Fontenay

2009/09/04 at 8:29 am

Posted in Data Warehouse