· 6 years ago · Jul 23, 2019, 11:20 PM
1BEGIN TRY
2 DROP TABLE [Dim].[Date]
3END TRY
4
5BEGIN CATCH
6 /*No Action*/
7END CATCH
8
9CREATE TABLE [Dim].[Date]
10(
11 [DateKey] INT primary key,
12 [Date] DATETIME,
13 [FullDate] CHAR(10),-- Date in MM-dd-yyyy format
14 [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
15 [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
16 [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
17 [DayOfWeek] CHAR(1),-- First Day Sunday=1 and Saturday=7
18 [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
19 [DayOfWeekInYear] VARCHAR(2),
20 [DayOfQuarter] VARCHAR(3),
21 [DayOfYear] VARCHAR(3),
22 [WeekOfMonth] VARCHAR(1),-- Week Number of Month
23 [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
24 [WeekOfYear] VARCHAR(2),--Week Number of the Year
25 [Month] VARCHAR(2), --Number of the Month 1 to 12
26 [MonthName] VARCHAR(9),--January, February etc
27 [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
28 [Quarter] CHAR(1),
29 [QuarterName] VARCHAR(9),--First,Second..
30 [Year] CHAR(4),-- Year value of Date stored in Row
31 [YearName] CHAR(7), --CY 2012,CY 2013
32 [MonthYear] CHAR(10), --Jan-2013,Feb-2013
33 [MMYYYY] CHAR(6),
34 [FirstDayOfMonth] DATE,
35 [LastDayOfMonth] DATE,
36 [FirstDayOfQuarter] DATE,
37 [LastDayOfQuarter] DATE,
38 [FirstDayOfYear] DATE,
39 [LastDayOfYear] DATE,
40 [IsHoliday] BIT,-- Flag 1=National Holiday, 0-No National Holiday
41 [IsWeekday] BIT,-- 0=Week End ,1=Week Day
42 [HolidayName] VARCHAR(50),--Name of Holiday in US
43)
44GO
45
46--=========================================================================================
47--Specify Start Date and End date here
48--Value of Start Date Must be Less than Your End Date
49--=========================================================================================
50
51DECLARE @StartDate DATETIME = '12/29/2014' --Starting value of Date Range
52DECLARE @EndDate DATETIME = '01/01/2100' --End Value of Date Range
53
54--Temporary Variables To Hold the Values During Processing of Each Date of Year
55DECLARE
56 @DayOfWeekInMonth INT,
57 @DayOfWeekInYear INT,
58 @DayOfQuarter INT,
59 @WeekOfMonth INT,
60 @CurrentYear INT,
61 @CurrentMonth INT,
62 @CurrentQuarter INT
63
64/*Table Data type to store the day of week count for the month and year*/
65DECLARE @DayOfWeek TABLE
66(
67 DOW INT,
68 MonthCount INT,
69 QuarterCount INT,
70 YearCount INT
71)
72
73INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
74INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
75INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
76INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
77INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
78INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
79INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
80
81--Extract and assign various parts of Values from Current Date to Variable
82
83DECLARE @CurrentDate AS DATETIME = @StartDate
84SET @CurrentMonth = DATEPART(MM, @CurrentDate)
85SET @CurrentYear = DATEPART(YY, @CurrentDate)
86SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
87
88/********************************************************************************************/
89--Proceed only if Start Date(Current date) is less than End date you specified above
90
91WHILE @CurrentDate < @EndDate
92/*Begin day of week logic*/
93BEGIN
94 /*Check for Change in Month of the Current date if Month changed then
95 Change variable value*/
96 IF @CurrentMonth != DATEPART(MM, @CurrentDate)
97 BEGIN
98 UPDATE @DayOfWeek
99 SET [MonthCount] = 0
100 SET @CurrentMonth = DATEPART(MM, @CurrentDate)
101 END
102
103 /* Check for Change in Quarter of the Current date if Quarter changed then change
104 Variable value*/
105 IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
106 BEGIN
107 UPDATE @DayOfWeek
108 SET [QuarterCount] = 0
109 SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
110 END
111
112 /* Check for Change in Year of the Current date if Year changed then change
113 Variable value*/
114 IF @CurrentYear != DATEPART(YY, @CurrentDate)
115 BEGIN
116 UPDATE @DayOfWeek
117 SET YearCount = 0
118 SET @CurrentYear = DATEPART(YY, @CurrentDate)
119 END
120
121 -- Set values in table data type created above from variables
122 UPDATE @DayOfWeek
123 SET
124 MonthCount = MonthCount + 1,
125 QuarterCount = QuarterCount + 1,
126 YearCount = YearCount + 1
127 WHERE DOW = DATEPART(DW, @CurrentDate)
128
129 SELECT
130 @DayOfWeekInMonth = MonthCount,
131 @DayOfQuarter = QuarterCount,
132 @DayOfWeekInYear = YearCount
133 FROM @DayOfWeek
134 WHERE DOW = DATEPART(DW, @CurrentDate)
135
136/*End day of week logic*/
137
138
139/* Populate Your Dimension Table with values*/
140
141 INSERT INTO [Dim].[Date]
142 SELECT
143
144 CONVERT (char(8),@CurrentDate,112) as 'DateKey',
145 @CurrentDate AS 'Date',
146 CONVERT (char(10),@CurrentDate,101) as 'FullDate',
147 DATEPART(DD, @CurrentDate) AS 'DayOfMonth',
148 --Apply Suffix values like 1st, 2nd 3rd etc..
149 CASE
150 WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
151 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
152 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
153 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
154 ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
155 END AS 'DaySuffix',
156
157 DATENAME(DW, @CurrentDate) AS 'DayName',
158 DATEPART(DW, @CurrentDate) AS 'DayOfWeek',
159 @DayOfWeekInMonth AS 'DayOfWeekInMonth',
160 @DayOfWeekInYear AS 'DayOfWeekInYear',
161 @DayOfQuarter AS 'DayOfQuarter',
162 DATEPART(DY, @CurrentDate) AS 'DayOfYear',
163 DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS 'WeekOfMonth',
164 (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS 'WeekOfQuarter',
165 DATEPART(WW, @CurrentDate) AS 'WeekOfYear',
166 DATEPART(MM, @CurrentDate) AS 'Month',
167 DATENAME(MM, @CurrentDate) AS 'MonthName',
168 CASE
169 WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
170 WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
171 WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
172 END AS 'MonthOfQuarter',
173 DATEPART(QQ, @CurrentDate) AS 'Quarter',
174 CASE DATEPART(QQ, @CurrentDate)
175 WHEN 1 THEN 'First'
176 WHEN 2 THEN 'Second'
177 WHEN 3 THEN 'Third'
178 WHEN 4 THEN 'Fourth'
179 END AS 'QuarterName',
180 DATEPART(YEAR, @CurrentDate) AS 'Year',
181 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS 'YearName',
182 LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS 'MonthYear',
183 RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS 'MMYYYY',
184 CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS 'FirstDayOfMonth',
185 CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS 'LastDayOfMonth',
186 DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS 'FirstDayOfQuarter',
187 DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS 'LastDayOfQuarter',
188 CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS 'FirstDayOfYear',
189 CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS 'LastDayOfYear',
190 NULL AS 'IsHoliday',
191 CASE DATEPART(DW, @CurrentDate)
192 WHEN 1 THEN 0
193 WHEN 2 THEN 1
194 WHEN 3 THEN 1
195 WHEN 4 THEN 1
196 WHEN 5 THEN 1
197 WHEN 6 THEN 1
198 WHEN 7 THEN 0
199 END AS 'IsWeekday',
200 NULL AS 'HolidayName'
201
202 SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
203END
204
205--============================================================================
206-- Step 3.
207-- Update Values of Holiday as per USA Govt. Declaration for National Holiday.
208--============================================================================
209
210/*Update HOLIDAY Field of USA In dimension*/
211 /* New Years Day - January 1 */
212 UPDATE [Dim].[Date]
213 SET HolidayName = 'New Year''s Day'
214 WHERE [Month] = 1 AND [DayOfMonth] = 1
215
216 /* Martin Luther King, Jr. Day - Third Monday in January starting in 1983 */
217 UPDATE [Dim].[Date]
218 SET HolidayName = 'Martin Luther King, Jr. Day'
219 WHERE
220 [Month] = 1 AND
221 [DayOfWeek] = 'Monday' AND
222 [Year] >= 1983 AND
223 DayOfWeekInMonth = 3
224
225 /* Valentine's Day - February 14 */
226 UPDATE [Dim].[Date]
227 SET HolidayName = 'Valentine''s Day'
228 WHERE
229 [Month] = 2 AND
230 [DayOfMonth] = 14
231
232 /* President's Day - Third Monday in February */
233 UPDATE [Dim].[Date]
234 SET HolidayName = 'President''s Day'
235 WHERE
236 [Month] = 2 AND
237 [DayOfWeek] = 'Monday' AND
238 [DayOfWeekInMonth] = 3
239
240 /* Saint Patrick's Day */
241 UPDATE [Dim].[Date]
242 SET HolidayName = 'Saint Patrick''s Day'
243 WHERE
244 [Month] = 3 AND
245 [DayOfMonth] = 17
246
247 /* Memorial Day - Last Monday in May */
248 UPDATE [Dim].[Date]
249 SET HolidayName = 'Memorial Day'
250 FROM [Dim].[Date]
251 WHERE DateKey IN
252 (
253 SELECT
254 MAX(DateKey)
255 FROM [Dim].[Date]
256 WHERE
257 [MonthName] = 'May' AND
258 [DayOfWeek] = 'Monday'
259 GROUP BY
260 [Year],
261 [Month]
262 )
263
264 /* Mother's Day - Second Sunday of May */
265 UPDATE [Dim].[Date]
266 SET HolidayName = 'Mother''s Day'
267 WHERE
268 [Month] = 5 AND
269 [DayOfWeek] = 'Sunday' AND
270 [DayOfWeekInMonth] = 2
271
272 /* Father's Day - Third Sunday of June */
273 UPDATE [Dim].[Date]
274 SET HolidayName = 'Father''s Day'
275 WHERE
276 [Month] = 6 AND
277 [DayOfWeek] = 'Sunday' AND
278 [DayOfWeekInMonth] = 3
279
280 /* Independence Day */
281 UPDATE [Dim].[Date]
282 SET HolidayName = 'Independance Day'
283 WHERE [Month] = 7 AND [DayOfMonth] = 4
284
285 /* Labor Day - First Monday in September */
286 UPDATE [Dim].[Date]
287 SET HolidayName = 'Labor Day'
288 FROM [Dim].[Date]
289 WHERE DateKey IN
290 (
291 SELECT
292 MIN(DateKey)
293 FROM [Dim].[Date]
294 WHERE
295 [MonthName] = 'September' AND
296 [DayOfWeek] = 'Monday'
297 GROUP BY
298 [Year],
299 [Month]
300 )
301
302 /* Columbus Day - Second MONDAY in October */
303 UPDATE [Dim].[Date]
304 SET HolidayName = 'Columbus Day'
305 WHERE
306 [Month] = 10 AND
307 [DayOfWeek] = 'Monday' AND
308 [DayOfWeekInMonth] = 2
309
310 /* Halloween - 10/31 */
311 UPDATE [Dim].[Date]
312 SET HolidayName = 'Halloween'
313 WHERE
314 [Month] = 10 AND
315 [DayOfMonth] = 31
316
317 /* Veterans Day - November 11 */
318 UPDATE [Dim].[Date]
319 SET HolidayName = 'Veterans Day'
320 WHERE
321 [Month] = 11 AND
322 [DayOfMonth] = 11
323
324 /* Thanksgiving - Fourth THURSDAY in November */
325 UPDATE [Dim].[Date]
326 SET HolidayName = 'Thanksgiving Day'
327 WHERE
328 [Month] = 11 AND
329 [DayOfWeek] = 'Thursday' AND
330 [DayOfWeekInMonth] = 4
331
332 /* Christmas */
333 UPDATE [Dim].[Date]
334 SET HolidayName = 'Christmas Day'
335 WHERE [Month] = 12 AND
336 [DayOfMonth] = 25
337
338 /* Election Day - The first Tuesday after the first Monday in November */
339 BEGIN
340 DECLARE @Holidays TABLE
341 (
342 [ID] INT IDENTITY(1,1),
343 [DateID] INT,
344 [Week] TINYINT,
345 [Year] CHAR(4),
346 [Day] CHAR(2)
347 )
348
349 INSERT INTO @Holidays([DateID], [Year], [Day])
350 SELECT
351 [DateKey],
352 [Year],
353 [DayOfMonth]
354 FROM [Dim].[Date]
355 WHERE
356 [Month] = 11 AND
357 [DayOfWeek] = 'Monday'
358 ORDER BY
359 [Year],
360 [DayOfMonth]
361
362 DECLARE @CNTR INT,
363 @POS INT,
364 @STARTYEAR INT,
365 @ENDYEAR INT,
366 @MINDAY INT
367
368 SELECT @CURRENTYEAR = MIN([Year])
369 , @STARTYEAR = MIN([Year])
370 , @ENDYEAR = MAX([Year])
371 FROM @Holidays
372
373 WHILE @CURRENTYEAR <= @ENDYEAR
374 BEGIN
375 SELECT @CNTR = COUNT([Year])
376 FROM @Holidays
377 WHERE [Year] = @CURRENTYEAR
378
379 SET @POS = 1
380
381 WHILE @POS <= @CNTR
382 BEGIN
383 SELECT @MINDAY = MIN(DAY)
384 FROM @Holidays
385 WHERE
386 [Year] = @CURRENTYEAR AND
387 [Week] IS NULL
388
389 UPDATE @Holidays
390 SET [Week] = @POS
391 WHERE
392 [Year] = @CURRENTYEAR AND
393 [Day] = @MINDAY
394
395 SELECT @POS = @POS + 1
396 END
397
398 SELECT @CURRENTYEAR = @CURRENTYEAR + 1
399 END
400
401 UPDATE [Dim].[Date]
402 SET HolidayName = 'Election Day'
403 FROM [Dim].[Date] DT
404 JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
405 WHERE
406 [Week] = 1
407 END
408 --set flag for USA holidays in Dimension
409 UPDATE [Dim].[Date]
410 SET IsHoliday = CASE WHEN HolidayName IS NULL THEN 0
411 WHEN HolidayName IS NOT NULL THEN 1 END
412
413/*****************************************************************************************/
414
415/* Add Fiscal Calendar columns into table DimDate */
416
417ALTER TABLE [Dim].[Date] ADD
418 [FiscalDayOfYear] VARCHAR(3),
419 [FiscalWeekOfYear] VARCHAR(3),
420 [FiscalMonth] VARCHAR(2),
421 [FiscalQuarter] CHAR(1),
422 [FiscalQuarterName] VARCHAR(9),
423 [FiscalYear] CHAR(4),
424 [FiscalYearName] CHAR(7),
425 [FiscalMonthYear] CHAR(10),
426 [FiscalMMYYYY] CHAR(6),
427 [FiscalFirstDayOfMonth] DATE,
428 [FiscalLastDayOfMonth] DATE,
429 [FiscalFirstDayOfQuarter] DATE,
430 [FiscalLastDayOfQuarter] DATE,
431 [FiscalFirstDayOfYear] DATE,
432 [FiscalLastDayOfYear] DATE
433
434GO
435
436/***************************************************************************
437The following section needs to be populated for defining the fiscal calendar
438***************************************************************************/
439
440DECLARE
441 @dtFiscalYearStart SMALLDATETIME = 'December 29, 2014',
442 @FiscalYear INT = 2015,
443 @LastYear INT = 2100,
444 @FirstLeapYearInPeriod INT = 2012
445
446/*****************************************************************************************/
447
448DECLARE
449 @iTemp INT,
450 @LeapWeek INT,
451 @CurrentDate DATETIME,
452 @FiscalDayOfYear INT,
453 @FiscalWeekOfYear INT,
454 @FiscalMonth INT,
455 @FiscalQuarter INT,
456 @FiscalQuarterName VARCHAR(10),
457 @FiscalYearName VARCHAR(7),
458 @LeapYear INT,
459 @FiscalFirstDayOfYear DATE,
460 @FiscalFirstDayOfQuarter DATE,
461 @FiscalFirstDayOfMonth DATE,
462 @FiscalLastDayOfYear DATE,
463 @FiscalLastDayOfQuarter DATE,
464 @FiscalLastDayOfMonth DATE
465
466/*Holds the years that have 455 in last quarter*/
467
468DECLARE @LeapTable TABLE (leapyear INT)
469
470/*TABLE to contain the fiscal year calendar*/
471
472DECLARE @tb TABLE
473(
474 [PeriodDate] DATETIME,
475 [FiscalDayOfYear] VARCHAR(3),
476 [FiscalWeekOfYear] VARCHAR(3),
477 [FiscalMonth] VARCHAR(2),
478 [FiscalQuarter] VARCHAR(1),
479 [FiscalQuarterName] VARCHAR(9),
480 [FiscalYear] VARCHAR(4),
481 [FiscalYearName] VARCHAR(7),
482 [FiscalMonthYear] VARCHAR(10),
483 [FiscalMMYYYY] VARCHAR(6),
484 [FiscalFirstDayOfMonth] DATE,
485 [FiscalLastDayOfMonth] DATE,
486 [FiscalFirstDayOfQuarter] DATE,
487 [FiscalLastDayOfQuarter] DATE,
488 [FiscalFirstDayOfYear] DATE,
489 [FiscalLastDayOfYear] DATE
490)
491
492/*Populate the table with all leap years*/
493
494SET @LeapYear = @FirstLeapYearInPeriod
495WHILE (@LeapYear < @LastYear)
496 BEGIN
497 INSERT INTO @leapTable VALUES (@LeapYear)
498 SET @LeapYear = @LeapYear + 6
499 END
500
501/*Initiate parameters before loop*/
502
503SET @CurrentDate = @dtFiscalYearStart
504SET @FiscalDayOfYear = 1
505SET @FiscalWeekOfYear = 1
506SET @FiscalMonth = 1
507SET @FiscalQuarter = 1
508SET @FiscalWeekOfYear = 1
509
510IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
511 BEGIN
512 SET @LeapWeek = 1
513 END
514 ELSE
515 BEGIN
516 SET @LeapWeek = 0
517 END
518
519/*******************************************************************************************/
520
521/* Loop on days in interval*/
522
523WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
524BEGIN
525
526/*SET fiscal Month*/
527 SELECT @FiscalMonth = CASE
528 /*
529 /*Use this section for a 4-5-4 calendar.
530 Every leap year the result will be a 4-5-5*/
531 WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
532 WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
533 WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
534 WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
535 WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
536 WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
537 WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
538 WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
539 WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
540 WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
541 WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11 /*5 weeks*/
542 WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap year)*/
543 */
544
545 /*Use this section for a 4-4-5 calendar.
546 Every leap year the result will be a 4-5-5*/
547 WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
548 WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
549 WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
550 WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
551 WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
552 WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
553 WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
554 WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
555 WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
556 WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
557 WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
558 WHEN @FiscalWeekOfYear BETWEEN (48 + @leapWeek) AND (52 + @leapWeek) THEN 12 /*5 weeks*/
559
560 END
561
562 /*SET Fiscal Quarter*/
563 SELECT @FiscalQuarter = CASE
564 WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
565 WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
566 WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
567 WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
568 END
569
570 SELECT @FiscalQuarterName = CASE
571 WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
572 WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
573 WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
574 WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
575 END
576
577 /*Set Fiscal Year Name*/
578 SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)
579
580 INSERT INTO @tb
581 (
582 PeriodDate,
583 FiscalDayOfYear,
584 FiscalWeekOfYear,
585 FiscalMonth,
586 FiscalQuarter,
587 FiscalQuarterName,
588 FiscalYear,
589 FiscalYearName
590 ) VALUES (
591 @CurrentDate,
592 @FiscalDayOfYear,
593 @FiscalWeekOfYear,
594 @FiscalMonth,
595 @FiscalQuarter,
596 @FiscalQuarterName,
597 @FiscalYear,
598 @FiscalYearName
599 )
600
601 /*SET next day*/
602 SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
603 SET @FiscalDayOfYear = @FiscalDayOfYear + 1
604 SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
605
606
607 IF (@FiscalWeekOfYear > (52+@LeapWeek))
608 BEGIN
609 /*Reset a new year*/
610 SET @FiscalDayOfYear = 1
611 SET @FiscalWeekOfYear = 1
612 SET @FiscalYear = @FiscalYear + 1
613 IF (EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
614 BEGIN
615 SET @LeapWeek = 1
616 END
617 ELSE
618 BEGIN
619 SET @LeapWeek = 0
620 END
621 END
622END
623
624/********************************************************************************************/
625
626/*Set first and last days of the fiscal months*/
627UPDATE @tb
628SET
629 FiscalFirstDayOfMonth = minmax.StartDate,
630 FiscalLastDayOfMonth = minmax.EndDate
631FROM
632 @tb t,
633 (
634 SELECT
635 FiscalMonth,
636 FiscalQuarter,
637 FiscalYear,
638 MIN(PeriodDate) AS StartDate,
639 MAX(PeriodDate) AS EndDate
640 FROM @tb
641 GROUP BY
642 FiscalMonth,
643 FiscalQuarter,
644 FiscalYear
645 ) minmax
646
647WHERE
648 t.FiscalMonth = minmax.FiscalMonth AND
649 t.FiscalQuarter = minmax.FiscalQuarter AND
650 t.FiscalYear = minmax.FiscalYear
651
652/*Set first and last days of the fiscal quarters*/
653
654UPDATE @tb
655SET FiscalFirstDayOfQuarter = minmax.StartDate,
656 FiscalLastDayOfQuarter = minmax.EndDate
657FROM
658 @tb t,
659 (
660 SELECT
661 FiscalQuarter,
662 FiscalYear,
663 MIN(PeriodDate) as StartDate,
664 MAX(PeriodDate) as EndDate
665 FROM
666 @tb
667 GROUP BY
668 FiscalQuarter,
669 FiscalYear
670 ) minmax
671WHERE
672 t.FiscalQuarter = minmax.FiscalQuarter AND
673 t.FiscalYear = minmax.FiscalYear
674
675/*Set first and last days of the fiscal years*/
676
677UPDATE @tb
678SET
679 FiscalFirstDayOfYear = minmax.StartDate,
680 FiscalLastDayOfYear = minmax.EndDate
681FROM
682@tb t,
683(
684 SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
685 FROM @tb
686 GROUP BY FiscalYear
687) minmax
688WHERE
689 t.FiscalYear = minmax.FiscalYear
690
691/*Set FiscalYearMonth*/
692UPDATE @tb
693SET
694 FiscalMonthYear =
695 CASE FiscalMonth
696 WHEN 1 THEN 'Jan'
697 WHEN 2 THEN 'Feb'
698 WHEN 3 THEN 'Mar'
699 WHEN 4 THEN 'Apr'
700 WHEN 5 THEN 'May'
701 WHEN 6 THEN 'Jun'
702 WHEN 7 THEN 'Jul'
703 WHEN 8 THEN 'Aug'
704 WHEN 9 THEN 'Sep'
705 WHEN 10 THEN 'Oct'
706 WHEN 11 THEN 'Nov'
707 WHEN 12 THEN 'Dec'
708 END + '-' + CONVERT(VARCHAR, FiscalYear)
709
710/*Set FiscalMMYYYY*/
711UPDATE @tb
712SET
713 FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)
714
715/********************************************************************************************/
716
717UPDATE [Dim].[Date]
718 SET FiscalDayOfYear = a.FiscalDayOfYear
719 , FiscalWeekOfYear = a.FiscalWeekOfYear
720 , FiscalMonth = a.FiscalMonth
721 , FiscalQuarter = a.FiscalQuarter
722 , FiscalQuarterName = a.FiscalQuarterName
723 , FiscalYear = a.FiscalYear
724 , FiscalYearName = a.FiscalYearName
725 , FiscalMonthYear = a.FiscalMonthYear
726 , FiscalMMYYYY = a.FiscalMMYYYY
727 , FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
728 , FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
729 , FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
730 , FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
731 , FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
732 , FiscalLastDayOfYear = a.FiscalLastDayOfYear
733FROM @tb a
734 INNER JOIN [Dim].[Date] b ON a.PeriodDate = b.[Date]
735
736/********************************************************************************************/
737
738SELECT * FROM [Dim].[Date]