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