Recientemente me he visto en la necesidad de crear una dimensión de tiempo en un nuevo Data Warehouse en una empresa financiera, por lo que tuve que utilizar un script de SQL Server para crear la tabla y rellenarla. Os lo dejo por si os véis en mi misma situación, que tengáis como mínimo por dónde empezar:
CREATE TABLE TIME (
TIME_KEY smalldatetime,
[DAY] int,
[MONTH] int,
[QUARTER] int,
[SEMESTER] int,
[HALF] int,
[YEAR] int,
[WEEKDAY] int,
[WEEK_OF_YEAR] int,
[MONTH_NAME] varchar(20),
[QUARTER_NAME] varchar(2),
[SEMESTER_NAME] varchar(2),
[HALF_NAME] varchar(2),
[WEEKDAY_NAME] varchar(10),
[DATE] [smalldatetime],
PRIMARY KEY CLUSTERED (TIME_KEY)
)
SET DATEFORMAT dmy
DECLARE @startDate SMALLDATETIME
DECLARE @currentDate AS SMALLDATETIME
DECLARE @maxDate AS DATETIME
DECLARE @halfNbr AS INT
DECLARE @semesterNbr AS INT
DECLARE @monthName AS VARCHAR(50)
DECLARE @quarterName AS VARCHAR(2)
DECLARE @semesterName AS VARCHAR(2)
DECLARE @halfName AS VARCHAR(2)
DECLARE @weekDayName AS VARCHAR(10)
SET @startDate = '20040101'
SET @maxDate = DATEADD(YEAR, 25, GETDATE())
SET @currentDate = @startDate
WHILE (@currentDate < @maxDate)
BEGIN
SET @semesterNbr = CASE WHEN MONTH(@currentDate) < 5 THEN 1 WHEN MONTH(@currentDate) > 8 THEN 3 ELSE 2 END
SET @halfNbr = CASE WHEN MONTH(@currentDate) < 7 THEN 1 ELSE 2 END
SET @monthName = DATENAME(MONTH, @currentDate)
SET @quarterName = 'Q' + CONVERT(char(1), DATEPART(QUARTER, @currentDate))
SET @semesterName = 'S' + CONVERT(char(1), @semesterNbr)
SET @halfName = 'H' + CONVERT(char(1), @halfNbr)
SET @weekDayName = DATENAME(WEEKDAY, @currentDate)
INSERT INTO [TIME]
VALUES(@currentDate, DAY(@currentDate), MONTH(@currentDate), DATEPART(QUARTER,@currentDate),
@semesterNbr, @halfNbr, YEAR(@currentDate), DATEPART(WEEKDAY, @currentDate),
DATEPART(WEEK,@currentDate), @monthName, @quarterName, @semesterName, @halfName,
DATENAME(WEEKDAY, @currentDate), @currentDate)
SET @currentDate = DATEADD(DAY, 1, @currentDate)
END
Posts
