Integration.PopulateDateDimensionForYear
Documentation | WideWorldImportersDW |
Schema | Integration |
Name | PopulateDateDimensionForYear |
Takes a year and ensures that all dates for that year are populated in the Dimension.Date table.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | IN | YearNumber | int |
Script
CREATE PROCEDURE Integration.PopulateDateDimensionForYear @YearNumber int WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @DateCounter date = DATEFROMPARTS(@YearNumber, 1, 1); BEGIN TRY; BEGIN TRAN; WHILE YEAR(@DateCounter) = @YearNumber BEGIN IF NOT EXISTS (SELECT 1 FROM Dimension.[Date] WHERE [Date] = @DateCounter) BEGIN INSERT Dimension.[Date] ([Date], [Day Number], [Day], [Month], [Short Month], [Calendar Month Number], [Calendar Month Label], [Calendar Year], [Calendar Year Label], [Fiscal Month Number], [Fiscal Month Label], [Fiscal Year], [Fiscal Year Label], [ISO Week Number]) SELECT [Date], [Day Number], [Day], [Month], [Short Month], [Calendar Month Number], [Calendar Month Label], [Calendar Year], [Calendar Year Label], [Fiscal Month Number], [Fiscal Month Label], [Fiscal Year], [Fiscal Year Label], [ISO Week Number] FROM Integration.GenerateDateDimensionColumns(@DateCounter); END; SET @DateCounter = DATEADD(day, 1, @DateCounter); END; COMMIT; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK; PRINT N'Unable to populate dates for the year'; THROW; RETURN -1; END CATCH; RETURN 0; END; |
Exported: 2019-02-04 23:05, Last imported: 2018-08-10 09:59