Expand Period table with Hours

The Period Table task that comes with the ETL Studio creates Years, Months, Dates etc.

It does not, however, create hours, minutes, seconds.

In this example I will show you how you can create a new Period table, including hours, from the existing Period table.

My ETL project looks like this:

mceclip0.png

The Bulk Import task, the Period Table task and the Save task are all standard tasks that I have added without modifications.

The code for the Create HoursTable with 1-24 script:

IF [HoursTable].exists=true THEN
DROP [HoursTable]
ELSE
CREATE table [HoursTable] (Hours INT)
DECLARE @counter AS INT
SET @counter = 1
WHILE @counter <= 24
INSERT INTO [HoursTable] (Hours) VALUES (@counter)
SET @counter = @counter + 1
LOOP
END IF

The code for the PeriodTableHours import task:

SELECT * FROM [PeriodTable] CROSS JOIN [HoursTable]

The cross join will result in a new table like this - where hours are being repeated 1-24 for every date:

mceclip1.png

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.