List

TSQL Script #1 – Create dbo.DimDate table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
BEGIN TRY
    DROP TABLE [dbo].[DimDate]
END TRY

BEGIN CATCH
    /*No Action*/
END CATCH

/**********************************************************************************/

CREATE TABLE    [dbo].[DimDate]
    (   [DateKey] INT primary key,
        [Date] DATETIME,
        [FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
        [FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
        [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
        [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
        [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
        [DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
        [DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
        [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
        [DayOfWeekInYear] VARCHAR(2),
        [DayOfQuarter] VARCHAR(3),
        [DayOfYear] VARCHAR(3),
        [WeekOfMonth] VARCHAR(1),-- Week Number of Month
        [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
        [WeekOfYear] VARCHAR(2),--Week Number of the Year
        [Month] VARCHAR(2), --Number of the Month 1 to 12
        [MonthName] VARCHAR(9),--January, February etc
        [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
        [Quarter] CHAR(1),
        [QuarterName] VARCHAR(9),--First,Second..
        [Year] CHAR(4),-- Year value of Date stored in Row
        [YearName] CHAR(7), --CY 2012,CY 2013
        [MonthYear] CHAR(10), --Jan-2013,Feb-2013
        [MMYYYY] CHAR(6),
        [FirstDayOfMonth] DATE,
        [LastDayOfMonth] DATE,
        [FirstDayOfQuarter] DATE,
        [LastDayOfQuarter] DATE,
        [FirstDayOfYear] DATE,
        [LastDayOfYear] DATE,
        [IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
        [IsWeekday] BIT,-- 0=Week End ,1=Week Day
        [HolidayUSA] VARCHAR(50),--Name of Holiday in US
        [IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
        [HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
    )
GO

TSQL Script #2 – Populate DimDate table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date

DECLARE @StartDate DATETIME = '01/01/2015' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2025' --End Value of Date Range

--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
    @DayOfWeekInMonth INT,
    @DayOfWeekInYear INT,
    @DayOfQuarter INT,
    @WeekOfMonth INT,
    @CurrentYear INT,
    @CurrentMonth INT,
    @CurrentQuarter INT

/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)

INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)

--Extract and assign various parts of Values from Current Date to Variable

DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)

/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above

WHILE @CurrentDate < @EndDate
BEGIN
 
/*Begin day of week logic*/

         /*Check for Change in Month of the Current date if Month changed then
          Change variable value*/

    IF @CurrentMonth != DATEPART(MM, @CurrentDate)
    BEGIN
        UPDATE @DayOfWeek
        SET MonthCount = 0
        SET @CurrentMonth = DATEPART(MM, @CurrentDate)
    END

        /* Check for Change in Quarter of the Current date if Quarter changed then change
         Variable value*/


    IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
    BEGIN
        UPDATE @DayOfWeek
        SET QuarterCount = 0
        SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
    END
       
        /* Check for Change in Year of the Current date if Year changed then change
         Variable value*/

   

    IF @CurrentYear != DATEPART(YY, @CurrentDate)
    BEGIN
        UPDATE @DayOfWeek
        SET YearCount = 0
        SET @CurrentYear = DATEPART(YY, @CurrentDate)
    END
   
        -- Set values in table data type created above from variables

    UPDATE @DayOfWeek
    SET
        MonthCount = MonthCount + 1,
        QuarterCount = QuarterCount + 1,
        YearCount = YearCount + 1
    WHERE DOW = DATEPART(DW, @CurrentDate)

    SELECT
        @DayOfWeekInMonth = MonthCount,
        @DayOfQuarter = QuarterCount,
        @DayOfWeekInYear = YearCount
    FROM @DayOfWeek
    WHERE DOW = DATEPART(DW, @CurrentDate)
   
/*End day of week logic*/


/* Populate Your Dimension Table with values*/
   
    INSERT INTO [dbo].[DimDate]
    SELECT
       
        CONVERT (char(8),@CurrentDate,112) as DateKey,
        @CurrentDate AS Date,
        CONVERT (char(10),@CurrentDate,103) as FullDateUK,
        CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
        DATEPART(DD, @CurrentDate) AS DayOfMonth,
        --Apply Suffix values like 1st, 2nd 3rd etc..
        CASE
            WHEN DATEPART(DD,@CurrentDate) IN (11,12,13)
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
            WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3
            THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
            ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
            END AS DaySuffix,
       
        DATENAME(DW, @CurrentDate) AS DayName,
        DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,

        -- check for day of week as Per US and change it as per UK format
        CASE DATEPART(DW, @CurrentDate)
            WHEN 1 THEN 7
            WHEN 2 THEN 1
            WHEN 3 THEN 2
            WHEN 4 THEN 3
            WHEN 5 THEN 4
            WHEN 6 THEN 5
            WHEN 7 THEN 6
            END
            AS DayOfWeekUK,
       
        @DayOfWeekInMonth AS DayOfWeekInMonth,
        @DayOfWeekInYear AS DayOfWeekInYear,
        @DayOfQuarter AS DayOfQuarter,
        DATEPART(DY, @CurrentDate) AS DayOfYear,
        DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR,
        DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR,
        DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
        (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
        @CurrentDate) / 7) + 1 AS WeekOfQuarter,
        DATEPART(WW, @CurrentDate) AS WeekOfYear,
        DATEPART(MM, @CurrentDate) AS Month,
        DATENAME(MM, @CurrentDate) AS MonthName,
        CASE
            WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
            WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
            WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
            END AS MonthOfQuarter,
        DATEPART(QQ, @CurrentDate) AS Quarter,
        CASE DATEPART(QQ, @CurrentDate)
            WHEN 1 THEN 'First'
            WHEN 2 THEN 'Second'
            WHEN 3 THEN 'Third'
            WHEN 4 THEN 'Fourth'
            END AS QuarterName,
        DATEPART(YEAR, @CurrentDate) AS Year,
        'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
        LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR,
        DATEPART(YY, @CurrentDate)) AS MonthYear,
        RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) +
        CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
        CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
        @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
        CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
        (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1,
        @CurrentDate)))) AS LastDayOfMonth,
        DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
        DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
        CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY,
        @CurrentDate))) AS FirstDayOfYear,
        CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY,
        @CurrentDate))) AS LastDayOfYear,
        NULL AS IsHolidayUSA,
        CASE DATEPART(DW, @CurrentDate)
            WHEN 1 THEN 0
            WHEN 2 THEN 1
            WHEN 3 THEN 1
            WHEN 4 THEN 1
            WHEN 5 THEN 1
            WHEN 6 THEN 1
            WHEN 7 THEN 0
            END AS IsWeekday,
        NULL AS HolidayUSA, Null, Null

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END

TSQL Script #3 – Update UK Holiday values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/*Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday*/
   
-- Good Friday  April 18
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Good Friday'
    WHERE [Month] = 4 AND [DayOfMonth]  = 18

-- Easter Monday  April 21
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Easter Monday'
    WHERE [Month] = 4 AND [DayOfMonth]  = 21

-- Early May Bank Holiday   May 5
   UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Early May Bank Holiday'
    WHERE [Month] = 5 AND [DayOfMonth]  = 5

-- Spring Bank Holiday  May 26
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Spring Bank Holiday'
    WHERE [Month] = 5 AND [DayOfMonth]  = 26

-- Summer Bank Holiday  August 25
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Summer Bank Holiday'
    WHERE [Month] = 8 AND [DayOfMonth]  = 25

-- Boxing Day  December 26     
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Boxing Day'
    WHERE [Month] = 12 AND [DayOfMonth]  = 26  

--CHRISTMAS
    UPDATE [dbo].[DimDate]
        SET HolidayUK = 'Christmas Day'
    WHERE [Month] = 12 AND [DayOfMonth]  = 25

--New Years Day
    UPDATE [dbo].[DimDate]
        SET HolidayUK  = 'New Year''s Day'
    WHERE [Month] = 1 AND [DayOfMonth] = 1

--Update flag for UK Holidays 1= Holiday, 0=No Holiday
   
    UPDATE [dbo].[DimDate]
        SET IsHolidayUK  = CASE WHEN HolidayUK   IS NULL
        THEN 0 WHEN HolidayUK   IS NOT NULL THEN 1 END

TSQL Script #4 – Update US Holiday values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
/*Update HOLIDAY Field of USA In dimension*/
    DECLARE
    @CurrentYear INT

    /*THANKSGIVING - Fourth THURSDAY in November*/
    UPDATE [dbo].[DimDate]
        SET [HolidayUSA] = 'Thanksgiving Day'
    WHERE
        [Month] = 11
        AND [DayName] = 'Thursday'
        AND [WeekofMonth] = 4

    /*CHRISTMAS*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Christmas Day'
       
    WHERE [Month] = 12 AND [DayOfMonth]  = 25

    /*4th of July*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Independance Day'
    WHERE [Month] = 7 AND [DayOfMonth] = 4

    /*New Years Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'New Year''s Day'
    WHERE [Month] = 1 AND [DayOfMonth] = 1

    /*Memorial Day - Last Monday in May*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Memorial Day'
    FROM [dbo].[DimDate]
    WHERE DateKey IN
        (
        SELECT
            MAX(DateKey)
        FROM [dbo].[DimDate]
        WHERE
            [MonthName] = 'May'
            AND [DayName]  = 'Monday'
        GROUP BY
            [Year],
            [Month]
        )

    /*Labor Day - First Monday in September*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Labor Day'
    FROM [dbo].[DimDate]
    WHERE DateKey IN
        (
        SELECT
            MIN(DateKey)
        FROM [dbo].[DimDate]
        WHERE
            [MonthName] = 'September'
            AND [DayName] = 'Monday'
        GROUP BY
            [Year],
            [Month]
        )

    /*Valentine's Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Valentine''s Day'
    WHERE
        [Month] = 2
        AND [DayOfMonth] = 14

    /*Saint Patrick's Day*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Saint Patrick''s Day'
    WHERE
        [Month] = 3
        AND [DayOfMonth] = 17

    /*Martin Luthor King Day - Third Monday in January starting in 1983*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Martin Luthor King Jr Day'
    WHERE
        [Month] = 1
        AND [DayName]  = 'Monday'
        AND [Year] >= 1983
        AND DayOfWeekInMonth = 3

    /*President's Day - Third Monday in February*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'President''s Day'
    WHERE
        [Month] = 2
        AND [DayName] = 'Monday'
        AND DayOfWeekInMonth = 3

    /*Mother's Day - Second Sunday of May*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Mother''s Day'
    WHERE
        [Month] = 5
        AND [DayName] = 'Sunday'
        AND DayOfWeekInMonth = 2

    /*Father's Day - Third Sunday of June*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Father''s Day'
    WHERE
        [Month] = 6
        AND [DayName] = 'Sunday'
        AND DayOfWeekInMonth = 3

    /*Halloween 10/31*/
    UPDATE [dbo].[DimDate]
        SET HolidayUSA = 'Halloween'
    WHERE
        [Month] = 10
        AND [DayOfMonth] = 31

    /*Election Day - The first Tuesday after the first Monday in November*/
    BEGIN
    DECLARE @Holidays TABLE (ID INT IDENTITY(1,1),
    DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

        INSERT INTO @Holidays(DateID, [Year],[Day])
        SELECT
            DateKey,
            [Year],
            [DayOfMonth]
        FROM [dbo].[DimDate]
        WHERE
            [Month] = 11
            AND [DayName] = 'Monday'
        ORDER BY
            YEAR,
            DayOfMonth

        DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT

        SELECT
            @CURRENTYEAR = MIN([Year])
            , @STARTYEAR = MIN([Year])
            , @ENDYEAR = MAX([Year])
        FROM @Holidays

        WHILE @CURRENTYEAR <= @ENDYEAR
        BEGIN
            SELECT @CNTR = COUNT([Year])
            FROM @Holidays
            WHERE [Year] = @CURRENTYEAR

            SET @POS = 1

            WHILE @POS <= @CNTR
            BEGIN
                SELECT @MINDAY = MIN(DAY)
                FROM @Holidays
                WHERE
                    [Year] = @CURRENTYEAR
                    AND [Week] IS NULL

                UPDATE @Holidays
                    SET [Week] = @POS
                WHERE
                    [Year] = @CURRENTYEAR
                    AND [Day] = @MINDAY

                SELECT @POS = @POS + 1
            END

            SELECT @CURRENTYEAR = @CURRENTYEAR + 1
        END

        UPDATE [dbo].[DimDate]
            SET HolidayUSA  = 'Election Day'               
        FROM [dbo].[DimDate] DT
            JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
        WHERE
            [Week] = 1
    END
    --set flag for USA holidays in Dimension
    UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA  IS NULL THEN 0 WHEN HolidayUSA  IS NOT NULL THEN 1 END
/*****************************************************************************************/

TSQL Script #5 – Extend DimDate tabe to include fiscal calendar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*Add Fiscal Calendar columns into table DimDate*/

ALTER TABLE [dbo].[DimDate] ADD
    [FiscalDayOfYear] VARCHAR(3),
    [FiscalWeekOfYear] VARCHAR(3),
    [FiscalMonth] VARCHAR(2),
    [FiscalQuarter] CHAR(1),
    [FiscalQuarterName] VARCHAR(9),
    [FiscalYear] CHAR(4),
    [FiscalYearName] CHAR(7),
    [FiscalMonthYear] CHAR(10),
    [FiscalMMYYYY] CHAR(6),
    [FiscalFirstDayOfMonth] DATE,
    [FiscalLastDayOfMonth] DATE,
    [FiscalFirstDayOfQuarter] DATE,
    [FiscalLastDayOfQuarter] DATE,
    [FiscalFirstDayOfYear] DATE,
    [FiscalLastDayOfYear] DATE
    GO

TSQL Script #6 – Populate Fiscal Calendar data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
/***************************************************************************
The following section needs to be populated for defining the fiscal calendar
***************************************************************************/


DECLARE
    @dtFiscalYearStart SMALLDATETIME = 'January 01, 1995',
    @FiscalYear INT = 1995,
    @LastYear INT = 2025,
    @FirstLeapYearInPeriod INT = 1996

/*****************************************************************************************/

DECLARE
    @iTemp INT,
    @LeapWeek INT,
    @CurrentDate DATETIME,
    @FiscalDayOfYear INT,
    @FiscalWeekOfYear INT,
    @FiscalMonth INT,
    @FiscalQuarter INT,
    @FiscalQuarterName VARCHAR(10),
    @FiscalYearName VARCHAR(7),
    @LeapYear INT,
    @FiscalFirstDayOfYear DATE,
    @FiscalFirstDayOfQuarter DATE,
    @FiscalFirstDayOfMonth DATE,
    @FiscalLastDayOfYear DATE,
    @FiscalLastDayOfQuarter DATE,
    @FiscalLastDayOfMonth DATE

/*Holds the years that have 455 in last quarter*/

DECLARE @LeapTable TABLE (leapyear INT)

/*TABLE to contain the fiscal year calendar*/

DECLARE @tb TABLE(
    PeriodDate DATETIME,
    [FiscalDayOfYear] VARCHAR(3),
    [FiscalWeekOfYear] VARCHAR(3),
    [FiscalMonth] VARCHAR(2),
    [FiscalQuarter] VARCHAR(1),
    [FiscalQuarterName] VARCHAR(9),
    [FiscalYear] VARCHAR(4),
    [FiscalYearName] VARCHAR(7),
    [FiscalMonthYear] VARCHAR(10),
    [FiscalMMYYYY] VARCHAR(6),
    [FiscalFirstDayOfMonth] DATE,
    [FiscalLastDayOfMonth] DATE,
    [FiscalFirstDayOfQuarter] DATE,
    [FiscalLastDayOfQuarter] DATE,
    [FiscalFirstDayOfYear] DATE,
    [FiscalLastDayOfYear] DATE)

/*Populate the table with all leap years*/

SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
    BEGIN
        INSERT INTO @leapTable VALUES (@LeapYear)
        SET @LeapYear = @LeapYear + 5
    END

/*Initiate parameters before loop*/

SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1

IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
    BEGIN
        SET @LeapWeek = 1
    END
    ELSE
    BEGIN
        SET @LeapWeek = 0
    END

/*******************************************************************************************/

/* Loop on days in interval*/

WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
   
/*SET fiscal Month*/
    SELECT @FiscalMonth = CASE
        /*Use this section for a 4-5-4 calendar.  
        Every leap year the result will be a 4-5-5*/

        WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11
/*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12
/*4 weeks (5 weeks on leap year)*/
       
/*Use this section for a 4-4-5 calendar.  
Every leap year the result will be a 4-5-5*/

        /*
        WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/

        WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
        WHEN @FiscalWeekOfYear BETWEEN 44 AND _
        (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
        */
    END

    /*SET Fiscal Quarter*/
    SELECT @FiscalQuarter = CASE
        WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
        WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
        WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
        WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
    END
   
    SELECT @FiscalQuarterName = CASE
        WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
        WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
        WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
        WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
    END
   
    /*Set Fiscal Year Name*/
    SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)

    INSERT INTO @tb (PeriodDate, FiscalDayOfYear, FiscalWeekOfYear,
    fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) VALUES
    (@CurrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth,
    @FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)

    /*SET next day*/
    SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
    SET @FiscalDayOfYear = @FiscalDayOfYear + 1
    SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1


    IF (@FiscalWeekOfYear > (52+@LeapWeek))
    BEGIN
        /*Reset a new year*/
        SET @FiscalDayOfYear = 1
        SET @FiscalWeekOfYear = 1
        SET @FiscalYear = @FiscalYear + 1
        IF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
        BEGIN
            SET @LeapWeek = 1
        END
        ELSE
        BEGIN
            SET @LeapWeek = 0
        END
    END
END

/********************************************************************************************/

/*Set first and last days of the fiscal months*/
UPDATE @tb
SET
    FiscalFirstDayOfMonth = minmax.StartDate,
    FiscalLastDayOfMonth = minmax.EndDate
FROM
@tb t,
    (
    SELECT FiscalMonth, FiscalQuarter, FiscalYear,
    MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
    FROM @tb
    GROUP BY FiscalMonth, FiscalQuarter, FiscalYear
    ) minmax
WHERE
    t.FiscalMonth = minmax.FiscalMonth AND
    t.FiscalQuarter = minmax.FiscalQuarter AND
    t.FiscalYear = minmax.FiscalYear

/*Set first and last days of the fiscal quarters*/

UPDATE @tb
SET
    FiscalFirstDayOfQuarter = minmax.StartDate,
    FiscalLastDayOfQuarter = minmax.EndDate
FROM
@tb t,
    (
    SELECT FiscalQuarter, FiscalYear, min(PeriodDate)
    as StartDate, max(PeriodDate) as EndDate
    FROM @tb
    GROUP BY FiscalQuarter, FiscalYear
    ) minmax
WHERE
    t.FiscalQuarter = minmax.FiscalQuarter AND
    t.FiscalYear = minmax.FiscalYear

/*Set first and last days of the fiscal years*/

UPDATE @tb
SET
    FiscalFirstDayOfYear = minmax.StartDate,
    FiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
    (
    SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
    FROM @tb
    GROUP BY FiscalYear
    ) minmax
WHERE
    t.FiscalYear = minmax.FiscalYear

/*Set FiscalYearMonth*/
UPDATE @tb
SET
    FiscalMonthYear =
        CASE FiscalMonth
        WHEN 1 THEN 'Jan'
        WHEN 2 THEN 'Feb'
        WHEN 3 THEN 'Mar'
        WHEN 4 THEN 'Apr'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'Jun'
        WHEN 7 THEN 'Jul'
        WHEN 8 THEN 'Aug'
        WHEN 9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
        END + '-' + CONVERT(VARCHAR, FiscalYear)

/*Set FiscalMMYYYY*/
UPDATE @tb
SET
    FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)

/********************************************************************************************/

UPDATE [dbo].[DimDate]
    SET
    FiscalDayOfYear = a.FiscalDayOfYear
    , FiscalWeekOfYear = a.FiscalWeekOfYear
    , FiscalMonth = a.FiscalMonth
    , FiscalQuarter = a.FiscalQuarter
    , FiscalQuarterName = a.FiscalQuarterName
    , FiscalYear = a.FiscalYear
    , FiscalYearName = a.FiscalYearName
    , FiscalMonthYear = a.FiscalMonthYear
    , FiscalMMYYYY = a.FiscalMMYYYY
    , FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
    , FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
    , FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
    , FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
    , FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
    , FiscalLastDayOfYear = a.FiscalLastDayOfYear
FROM @tb a
    INNER JOIN [dbo].[DimDate] b ON a.PeriodDate = b.[Date]

/********************************************************************************************/

Note: Originally posted here. Above code modified by the SharePoint Sheriff to run in MS SQL 2014 SP-1, tested and verified working as of 09/03/2015.

  Posts

February 14th, 2018

Invincible workflows

Today I encountered a list item that had two instances of the same workflow, both stuck on the first step. […]

May 17th, 2017

The case of the missing App Launcher

Today I deleted and recreated a SharePoint 2016 Web application and recreated it, without issue. Well, except that the new […]

March 10th, 2017

The server failed to process the request. Aka, the SharePoint version of PCLoadLetter

It’s Friday so I’ll keep this short The ongoing power struggle between SharePoint and it’s tamers continues. Today we’re faces […]

March 3rd, 2017

Search, search, search, all the day long…

So, I just rolled SharePoint 2016 into a test environment and launch Central Admin, and the first thing my eyes […]

May 5th, 2016

Oh, happy day!

SharePoint 2016 has been released to MSDN! Time to practice different upgrade strategies and see what this baby can do! […]

March 29th, 2016

Branding Gone Wild

Today I ran into a problematic sub-site in my SharePoint 2013 farm. This sub-site, named “Test”, was where I had […]

March 14th, 2016

SharePoint 2016 RTM Released Today

Today marks the beginning of the next chapter for SharePoint as Microsoft released SharePoint 2016 RTM today. Ready to get […]

October 13th, 2015

A SharePoint game of hide and seek!

Today I was tasked with creating a custom list. Pretty normal so far, right? Here’s where it gets interesting. The […]

October 9th, 2015

My first Hadoop install.

So, I built my first single node Hadoop cluster today using Ubuntu 14.04 LTS, Hadoop 2.7.1 and Oracle Java 1.7.0_80 […]

September 3rd, 2015

Creating a date dimension table in MS SQL

TSQL Script #1 – Create dbo.DimDate table 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849BEGIN TRY     DROP TABLE [dbo].[DimDate] END TRY BEGIN CATCH     […]