· 7 years ago · Oct 30, 2018, 11:06 AM
1/********************************************************************************************/
2-- Specify Start Date and End date here
3-- Value of Start Date Must be Less than Your End Date
4
5DECLARE v_StartDate DATETIME(3) DEFAULT '01/01/2013' -- Starting value of Date Range
6DECLARE v_EndDate DATETIME(3) DEFAULT '01/01/2015' -- End Value of Date Range
7
8-- Temporary Variables To Hold the Values During Processing of Each Date of Year
9DECLARE
10 v_DayOfWeekInMonth INT;
11 DECLARE v_DayOfWeekInYear INT;
12 DECLARE v_DayOfQuarter INT;
13 DECLARE v_WeekOfMonth INT;
14 DECLARE v_CurrentYear INT;
15 DECLARE v_CurrentMonth INT;
16 DECLARE v_CurrentQuarter INT
17
18/*Table Data type to store the day of week count for the month and year*/
19DROP TEMPORARY TABLE IF EXISTS @DayOfWeek;
20CREATE TEMPORARY TABLE @DayOfWeek (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
21
22INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
23INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
24INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
25INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
26INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
27INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
28INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
29
30-- Extract and assign various parts of Values from Current Date to Variable
31
32DECLARE v_CurrentDate DATETIME(3) DEFAULT v_StartDate
33SET v_CurrentMonth = DATEPART(MM, v_CurrentDate)
34SET v_CurrentYear = DATEPART(YY, v_CurrentDate)
35SET v_CurrentQuarter = DATEPART(QQ, v_CurrentDate)
36
37/********************************************************************************************/
38-- Proceed only if Start Date(Current date ) is less than End date you specified above
39
40WHILE v_CurrentDate < v_EndDate
41DO
42
43/*Begin day of week logic*/
44
45 /*Check for Change in Month of the Current date if Month changed then
46 Change variable value*/
47 IF v_CurrentMonth != DATEPART(MM, v_CurrentDate)
48 THEN
49 UPDATE @DayOfWeek
50 SET MonthCount = 0;
51 SET v_CurrentMonth = DATEPART(MM, v_CurrentDate);
52 END IF;
53
54 /* Check for Change in Quarter of the Current date if Quarter changed then change
55 Variable value*/
56
57 IF v_CurrentQuarter != DATEPART(QQ, v_CurrentDate)
58 THEN
59 UPDATE @DayOfWeek
60 SET QuarterCount = 0;
61 SET v_CurrentQuarter = DATEPART(QQ, v_CurrentDate);
62 END IF;
63
64 /* Check for Change in Year of the Current date if Year changed then change
65 Variable value*/
66
67
68 IF v_CurrentYear != DATEPART(YY, v_CurrentDate)
69 THEN
70 UPDATE @DayOfWeek
71 SET YearCount = 0;
72 SET v_CurrentYear = DATEPART(YY, v_CurrentDate);
73 END IF;
74
75 -- Set values in table data type created above from variables
76
77 UPDATE @DayOfWeek
78 SET
79 MonthCount = MonthCount + 1,
80 QuarterCount = QuarterCount + 1,
81 YearCount = YearCount + 1
82 WHERE DOW = DATEPART(DW, v_CurrentDate)
83
84 SELECT
85 MonthCount,
86 QuarterCount,
87 YearCount INTO v_DayOfWeekInMonth, v_DayOfQuarter, v_DayOfWeekInYear
88 FROM @DayOfWeek
89 WHERE DOW = DATEPART(DW, v_CurrentDate)
90
91/*End day of week logic*/
92
93
94/* Populate Your Dimension Table with values*/
95
96 INSERT INTO DimDate
97 SELECT
98
99 DATE_FORMAT (v_CurrentDate,'%Y%m%d') as DateKey,
100 v_CurrentDate AS Date,
101 DATE_FORMAT (v_CurrentDate,'%d/%m/%Y') as FullDateUK,
102 DATE_FORMAT (v_CurrentDate,'%m/%d/%Y') as FullDateUSA,
103 DATEPART(DD, v_CurrentDate) AS DayOfMonth,
104 -- Apply Suffix values like 1st, 2nd 3rd etc..
105 CASE
106 WHEN DATEPART(DD,v_CurrentDate) IN (11,12,13) _
107 THEN CONCAT(CAST(DATEPART(DD,v_CurrentDate) AS VARCHAR(1)) , 'th')
108 WHEN RIGHT(DATEPART(DD,v_CurrentDate),1) = 1 _
109 THEN CONCAT(CAST(DATEPART(DD,v_CurrentDate) AS VARCHAR(1)) , 'st')
110 WHEN RIGHT(DATEPART(DD,v_CurrentDate),1) = 2 _
111 THEN CONCAT(CAST(DATEPART(DD,v_CurrentDate) AS VARCHAR(1)) , 'nd')
112 WHEN RIGHT(DATEPART(DD,v_CurrentDate),1) = 3 _
113 THEN CONCAT(CAST(DATEPART(DD,v_CurrentDate) AS VARCHAR(1)) , 'rd')
114 ELSE CONCAT(CAST(DATEPART(DD,v_CurrentDate) AS VARCHAR(1)) , 'th')
115 END WHILE AS DaySuffix,
116
117 DATENAME(DW, v_CurrentDate) AS DayName,
118 DATEPART(DW, v_CurrentDate) AS DayOfWeekUSA,
119
120 -- check for day of week as Per US and change it as per UK format
121 CASE DATEPART(DW, v_CurrentDate)
122 WHEN 1 THEN 7
123 WHEN 2 THEN 1
124 WHEN 3 THEN 2
125 WHEN 4 THEN 3
126 WHEN 5 THEN 4
127 WHEN 6 THEN 5
128 WHEN 7 THEN 6
129 END
130 AS DayOfWeekUK,
131
132 v_DayOfWeekInMonth AS DayOfWeekInMonth,
133 v_DayOfWeekInYear AS DayOfWeekInYear,
134 v_DayOfQuarter AS DayOfQuarter,
135 DATEPART(DY, v_CurrentDate) AS DayOfYear,
136 DATEPART(WW, v_CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
137 DATEPART(MM, v_CurrentDate)) + Concat('/1/' , CONVERT(VARCHAR, _)
138 DATEPART(YY, v_CurrentDate))) AS WeekOfMonth,
139 (TIMESTAMPDIFF(DD, TIMESTAMPADD(QQ, TIMESTAMPDIFF(QQ, 0, v_CurrentDate), 0), _
140 v_CurrentDate) / 7) + 1 AS WeekOfQuarter,
141 DATEPART(WW, v_CurrentDate) AS WeekOfYear,
142 DATEPART(MM, v_CurrentDate) AS Month,
143 DATENAME(MM, v_CurrentDate) AS MonthName,
144 CASE
145 WHEN DATEPART(MM, v_CurrentDate) IN (1, 4, 7, 10) THEN 1
146 WHEN DATEPART(MM, v_CurrentDate) IN (2, 5, 8, 11) THEN 2
147 WHEN DATEPART(MM, v_CurrentDate) IN (3, 6, 9, 12) THEN 3
148 END AS MonthOfQuarter,
149 DATEPART(QQ, v_CurrentDate) AS Quarter,
150 CASE DATEPART(QQ, v_CurrentDate)
151 WHEN 1 THEN 'First'
152 WHEN 2 THEN 'Second'
153 WHEN 3 THEN 'Third'
154 WHEN 4 THEN 'Fourth'
155 END AS QuarterName,
156 DATEPART(YEAR, v_CurrentDate) AS Year,
157 CONCAT('CY ' , CONVERT(VARCHAR, DATEPART(YEAR, v_CurrentDate))) AS YearName,
158 LEFT(DATENAME(MM, v_CurrentDate), 3) + '-' , CONVERT(VARCHAR, _
159 DATEPART(YY, v_CurrentDate)) AS MonthYear,
160 RIGHT(Concat('0' , CONVERT(VARCHAR, DATEPART(MM, v_CurrentDate))),2) + _
161 CONVERT(VARCHAR, DATEPART(YY, v_CurrentDate)) AS MMYYYY,
162 CONVERT(CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
163 v_CurrentDate) - 1), v_CurrentDate))) AS FirstDayOfMonth,
164 CONVERT(CONVERT(DATE, TIMESTAMPADD(DD, - (DATEPART(DD, _
165 (TIMESTAMPADD(MM, 1, v_CurrentDate)))), TIMESTAMPADD(MM, 1, _
166 v_CurrentDate)))) AS LastDayOfMonth,
167 TIMESTAMPADD(QQ, TIMESTAMPDIFF(QQ, 0, v_CurrentDate), 0) AS FirstDayOfQuarter,
168 TIMESTAMPADD(QQ, TIMESTAMPDIFF(QQ, -1, v_CurrentDate), -1) AS LastDayOfQuarter,
169 CONVERT(Concat('01/01/' , CONVERT(VARCHAR, DATEPART(YY, _)
170 v_CurrentDate))) AS FirstDayOfYear,
171 CONVERT(Concat('12/31/' , CONVERT(VARCHAR, DATEPART(YY, _)
172 v_CurrentDate))) AS LastDayOfYear,
173 NULL AS IsHolidayUSA,
174 CASE DATEPART(DW, v_CurrentDate)
175 WHEN 1 THEN 0
176 WHEN 2 THEN 1
177 WHEN 3 THEN 1
178 WHEN 4 THEN 1
179 WHEN 5 THEN 1
180 WHEN 6 THEN 1
181 WHEN 7 THEN 0
182 END AS IsWeekday,
183 NULL AS HolidayUSA, Null, Null
184
185 SET v_CurrentDate = TIMESTAMPADD(DD, 1, v_CurrentDate)
186END
187
188/********************************************************************************************/
189
190Step 3.
191Update Values of Holiday as per UK Government Declaration for National Holiday.
192
193/*Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday*/
194
195-- Good Friday April 18
196 UPDATE DimDate
197 SET HolidayUK = 'Good Friday'
198 WHERE `Month` = 4 AND `DayOfMonth` = 18
199
200-- Easter Monday April 21
201 UPDATE DimDate
202 SET HolidayUK = 'Easter Monday'
203 WHERE `Month` = 4 AND `DayOfMonth` = 21
204
205-- Early May Bank Holiday May 5
206 UPDATE DimDate
207 SET HolidayUK = 'Early May Bank Holiday'
208 WHERE `Month` = 5 AND `DayOfMonth` = 5
209
210-- Spring Bank Holiday May 26
211 UPDATE DimDate
212 SET HolidayUK = 'Spring Bank Holiday'
213 WHERE `Month` = 5 AND `DayOfMonth` = 26
214
215-- Summer Bank Holiday August 25
216 UPDATE DimDate
217 SET HolidayUK = 'Summer Bank Holiday'
218 WHERE `Month` = 8 AND `DayOfMonth` = 25
219
220-- Boxing Day December 26
221 UPDATE DimDate
222 SET HolidayUK = 'Boxing Day'
223 WHERE `Month` = 12 AND `DayOfMonth` = 26
224
225-- CHRISTMAS
226 UPDATE DimDate
227 SET HolidayUK = 'Christmas Day'
228 WHERE `Month` = 12 AND `DayOfMonth` = 25
229
230-- New Years Day
231 UPDATE DimDate
232 SET HolidayUK = 'New Year''s Day'
233 WHERE `Month` = 1 AND `DayOfMonth` = 1
234
235-- Update flag for UK Holidays 1= Holiday, 0=No Holiday
236
237 UPDATE DimDate
238 SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _
239 THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
240
241
242Step 4.
243Update Values of Holiday as per USA Govt. Declaration for National Holiday.
244
245/*Update HOLIDAY Field of USA In dimension*/
246
247 /*THANKSGIVING - Fourth THURSDAY in November*/
248 UPDATE DimDate
249 SET HolidayUSA = 'Thanksgiving Day'
250 WHERE
251 `Month` = 11
252 AND `DayOfWeekUSA` = 'Thursday'
253 AND DayOfWeekInMonth = 4
254
255 /*CHRISTMAS*/
256 UPDATE DimDate
257 SET HolidayUSA = 'Christmas Day'
258
259 WHERE `Month` = 12 AND `DayOfMonth` = 25
260
261 /*4th of July*/
262 UPDATE DimDate
263 SET HolidayUSA = 'Independance Day'
264 WHERE `Month` = 7 AND `DayOfMonth` = 4
265
266 /*New Years Day*/
267 UPDATE DimDate
268 SET HolidayUSA = 'New Year''s Day'
269 WHERE `Month` = 1 AND `DayOfMonth` = 1
270
271 /*Memorial Day - Last Monday in May*/
272 UPDATE DimDate
273 SET HolidayUSA = 'Memorial Day'
274 WHERE DateKey IN
275 (
276 SELECT
277 MAX(DateKey)
278 FROM DimDate
279 WHERE
280 `MonthName` = 'May'
281 AND `DayOfWeekUSA` = 'Monday'
282 GROUP BY
283 `Year`,
284 `Month`
285 )
286
287 /*Labor Day - First Monday in September*/
288 UPDATE DimDate
289 SET HolidayUSA = 'Labor Day'
290 WHERE DateKey IN
291 (
292 SELECT
293 MIN(DateKey)
294 FROM DimDate
295 WHERE
296 `MonthName` = 'September'
297 AND `DayOfWeekUSA` = 'Monday'
298 GROUP BY
299 `Year`,
300 `Month`
301 )
302
303 /*Valentine's Day*/
304 UPDATE DimDate
305 SET HolidayUSA = 'Valentine''s Day'
306 WHERE
307 `Month` = 2
308 AND `DayOfMonth` = 14
309
310 /*Saint Patrick's Day*/
311 UPDATE DimDate
312 SET HolidayUSA = 'Saint Patrick''s Day'
313 WHERE
314 `Month` = 3
315 AND `DayOfMonth` = 17
316
317 /*Martin Luthor King Day - Third Monday in January starting in 1983*/
318 UPDATE DimDate
319 SET HolidayUSA = 'Martin Luthor King Jr Day'
320 WHERE
321 `Month` = 1
322 AND `DayOfWeekUSA` = 'Monday'
323 AND `Year` >= 1983
324 AND DayOfWeekInMonth = 3
325
326 /*President's Day - Third Monday in February*/
327 UPDATE DimDate
328 SET HolidayUSA = 'President''s Day'
329 WHERE
330 `Month` = 2
331 AND `DayOfWeekUSA` = 'Monday'
332 AND DayOfWeekInMonth = 3
333
334 /*Mother's Day - Second Sunday of May*/
335 UPDATE DimDate
336 SET HolidayUSA = 'Mother''s Day'
337 WHERE
338 `Month` = 5
339 AND `DayOfWeekUSA` = 'Sunday'
340 AND DayOfWeekInMonth = 2
341
342 /*Father's Day - Third Sunday of June*/
343 UPDATE DimDate
344 SET HolidayUSA = 'Father''s Day'
345 WHERE
346 `Month` = 6
347 AND `DayOfWeekUSA` = 'Sunday'
348 AND DayOfWeekInMonth = 3
349
350 /*Halloween 10/31*/
351 UPDATE DimDate
352 SET HolidayUSA = 'Halloween'
353 WHERE
354 `Month` = 10
355 AND `DayOfMonth` = 31
356
357 /*Election Day - The first Tuesday after the first Monday in November*/
358 BEGIN
359 DROP TEMPORARY TABLE IF EXISTS @Holidays;
360 CREATE TEMPORARY TABLE @Holidays (ID INT AUTO_INCREMENT, _
361 DateID int, Week TINYINT, YEAR CHR(4), DAY CHR(2))
362
363 INSERT INTO @Holidays(DateID, `Year`,`Day`)
364 SELECT
365 DateKey,
366 `Year`,
367 `DayOfMonth`
368 FROM DimDate
369 WHERE
370 `Month` = 11
371 AND `DayOfWeekUSA` = 'Monday'
372 ORDER BY
373 YEAR,
374 DayOfMonth
375
376 DECLARE v_CNTR INT; DECLARE v_POS INT; DECLARE v_STARTYEAR INT; DECLARE v_ENDYEAR INT; DECLARE v_MINDAY INT
377
378 SELECT
379 MIN(`Year`)
380 , MIN(`Year`)
381 , MAX(`Year`) INTO v_CurrentYear, v_STARTYEAR, v_ENDYEAR
382 FROM @Holidays
383
384 WHILE v_CurrentYear <= v_ENDYEAR
385 BEGIN
386 SELECT COUNT([Year]) INTO v_CNTR
387 FROM @Holidays
388 WHERE `Year` = v_CurrentYear
389
390 SET v_POS = 1
391
392 WHILE v_POS <= v_CNTR
393 DO
394 SELECT MIN(DAY) INTO v_MINDAY
395 FROM @Holidays
396 WHERE
397 `Year` = v_CurrentYear
398 AND `Week` IS NULL
399
400 UPDATE @Holidays
401 SET `Week` = v_POS
402 WHERE
403 `Year` = v_CurrentYear
404 AND `Day` = v_MINDAY
405
406 SET v_POS = v_POS + 1
407 END WHILE
408
409 SET v_CurrentYear = v_CurrentYear + 1
410 END
411
412 UPDATE DimDate DT
413 JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
414 SET HolidayUSA = 'Election Day'
415 WHERE
416 `Week` = 1
417 END
418 -- set flag for USA holidays in Dimension
419 UPDATE DimDate
420SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
421/*****************************************************************************************/
422
423SELECT * FROM DimDate