· 8 years ago · Jun 28, 2018, 11:24 PM
1/****** Object: StoredProcedure [dim].[Build_Dim_Date] Script Date: 28/06/2018 11:33:02 ******/
2SET ANSI_NULLS ON
3GO
4
5SET QUOTED_IDENTIFIER ON
6GO
7
8
9
10
11
12CREATE PROCEDURE [dim].[Build_Dim_Date]
13@StartDate date = NULL,
14@EndDate date = NULL
15AS
16BEGIN
17DROP TABLE IF EXISTS [dim].[DIM_DATE];
18CREATE TABLE [dim].[DIM_DATE]
19(
20 [DATE_KEY] [int] IDENTITY(1000,1) PRIMARY KEY NOT NULL,
21 [DATE_AK] [date] NOT NULL,
22 [CALENDAR_YEAR] [int] NOT NULL,
23 [CALENDAR_QUARTER] [int] NOT NULL,
24 [CALENDAR_QUARTER_ABBR] [nvarchar](10) NOT NULL,
25 [CALENDAR_QUARTER_NAME] [nvarchar](10) NOT NULL,
26 [CALENDAR_MONTH_NAME] [nvarchar](10) NOT NULL,
27 [CALENDAR_MONTH_ABBR] [nvarchar](10) NOT NULL,
28 [CALENDAR_MONTH] [int] NOT NULL,
29 [CALENDAR_YEAR_MONTH] [NVARCHAR](15) NOT NULL,
30 [CALENDAR_WEEK_OF_YEAR] [int] NOT NULL,
31 [CALENDAR_DAY_NAME] [nvarchar](10) NOT NULL,
32 [CALENDAR_DAY_NAME_ABBR] [nvarchar](10) NOT NULL,
33 [CALENDAR_DAY] [int] NOT NULL,
34 [CALENDAR_DAY_SUFFIX] [nvarchar](10) NOT NULL,
35 [ACCOUNTING_YEAR] [int] NOT NULL,
36 [ACCOUNTING_QUARTER] [int] NOT NULL,
37 [ACCOUNTING_QUARTER_ABBR] [nvarchar](10) NOT NULL,
38 [ACCOUNTING_QUARTER_NAME] [nvarchar](10) NOT NULL,
39 [ACCOUNTING_MONTH_NAME] [nvarchar](10) NOT NULL,
40 [ACCOUNTING_MONTH_ABBR] [nvarchar](10) NOT NULL,
41 [ACCOUNTING_MONTH] [int] NOT NULL,
42 [ACCOUNTING_YEAR_MONTH] [NVARCHAR](15) NOT NULL,
43 [ACCOUNTING_WEEK_OF_YEAR] [int] NOT NULL,
44 [ACCOUNTING_DAY_NAME] [nvarchar](10) NOT NULL,
45 [ACCOUNTING_DAY_NAME_ABBR] [nvarchar](10) NOT NULL,
46 [ACCOUNTING_DAY] [int] NOT NULL,
47 [ACCOUNTING_DAY_SUFFIX] [nvarchar](10) NOT NULL,
48 [DAY_OF_WEEK] [int] NOT NULL,
49 [DAY_OF_YEAR] [int] NOT NULL,
50 [DAYS_IN_MONTH] [int] NOT NULL,
51 [MONTH_DAYS_REMAINING] [int] NOT NULL,
52 [YEAR_WEEK] [int] NOT NULL,
53 [FIRST_DAY_IN_WEEK] [date] NOT NULL,
54 [LAST_DAY_IN_WEEK] [date] NOT NULL,
55 [FIRST_DAY_IN_MONTH] [date] NOT NULL,
56 [LAST_DAY_IN_MONTH] [date] NOT NULL,
57 [LAST_WORKING_DAY] [date] NOT NULL,
58 [FIRST_DAY_IN_QUARTER] [date] NOT NULL,
59 [LAST_DAY_IN_QUARTER] [date] NOT NULL,
60 [WEEK_ENDING_SATURDAY] [date] NOT NULL,
61 [WEEK_BEGINNING_SUNDAY] [date] NOT NULL
62
63)
64
65
66WHILE @StartDate <= @EndDate
67BEGIN
68INSERT INTO [dim].[DIM_DATE]
69(
70
71 [DATE_AK],
72 [CALENDAR_YEAR],
73 [CALENDAR_QUARTER],
74 [CALENDAR_QUARTER_ABBR],
75 [CALENDAR_QUARTER_NAME],
76 [CALENDAR_MONTH_NAME],
77 [CALENDAR_MONTH_ABBR],
78 [CALENDAR_MONTH],
79 [CALENDAR_YEAR_MONTH],
80 [CALENDAR_WEEK_OF_YEAR],
81 [CALENDAR_DAY_NAME],
82 [CALENDAR_DAY_NAME_ABBR],
83 [CALENDAR_DAY],
84 [CALENDAR_DAY_SUFFIX],
85 [ACCOUNTING_YEAR],
86 [ACCOUNTING_QUARTER],
87 [ACCOUNTING_QUARTER_ABBR],
88 [ACCOUNTING_QUARTER_NAME],
89 [ACCOUNTING_MONTH_NAME],
90 [ACCOUNTING_MONTH_ABBR],
91 [ACCOUNTING_MONTH],
92 [ACCOUNTING_YEAR_MONTH],
93 [ACCOUNTING_WEEK_OF_YEAR],
94 [ACCOUNTING_DAY_NAME],
95 [ACCOUNTING_DAY_NAME_ABBR],
96 [ACCOUNTING_DAY],
97 [ACCOUNTING_DAY_SUFFIX],
98 [DAY_OF_WEEK],
99 [DAY_OF_YEAR],
100 [DAYS_IN_MONTH],
101 [MONTH_DAYS_REMAINING],
102 [YEAR_WEEK],
103 [FIRST_DAY_IN_WEEK],
104 [LAST_DAY_IN_WEEK],
105 [FIRST_DAY_IN_MONTH],
106 [LAST_DAY_IN_MONTH],
107 [LAST_WORKING_DAY],
108 [FIRST_DAY_IN_QUARTER],
109 [LAST_DAY_IN_QUARTER],
110 [WEEK_ENDING_SATURDAY],
111 [WEEK_BEGINNING_SUNDAY]
112
113)
114
115SELECT
116
117
118CAST(@StartDate AS date) AS DATE_AK,
119
120CAST(YEAR(@StartDate) AS int) AS CALENDAR_YEAR,
121
122CAST
123(
124 CASE
125 WHEN MONTH(@StartDate) IN (1, 2, 3)
126 THEN '1'
127 WHEN MONTH(@StartDate) IN (4, 5, 6)
128 THEN '2'
129 WHEN MONTH(@StartDate) IN (7, 8, 9)
130 THEN '3'
131 WHEN MONTH(@StartDate) IN (10,11,12)
132 THEN '4'
133END AS int) AS CALENDAR_QUARTER,
134
135CAST
136(
137 CASE
138 WHEN MONTH(@StartDate) IN (1, 2, 3)
139 THEN 'Q1'
140 WHEN MONTH(@StartDate) IN (4, 5, 6)
141 THEN 'Q2'
142 WHEN MONTH(@StartDate) IN (7, 8, 9)
143 THEN 'Q3'
144 WHEN MONTH(@StartDate) IN (10,11,12)
145 THEN 'Q4'
146 END AS varchar
147) AS CALENDAR_QUARTER_ABBR,
148
149CAST
150(
151 CASE
152 WHEN MONTH(@StartDate) IN (1, 2, 3)
153 THEN 'First'
154 WHEN MONTH(@StartDate) IN (4, 5, 6)
155 THEN 'Second'
156 WHEN MONTH(@StartDate) IN (7, 8, 9)
157 THEN 'Third'
158 WHEN MONTH(@StartDate) IN (10, 11, 12)
159 THEN 'Fourth'
160 END AS varchar
161) AS CALENDAR_QUARTER_NAME,
162
163CAST(DATENAME(MM,@StartDate) AS varchar )AS CALENDAR_MONTH_NAME,
164
165CAST(
166 CASE
167 WHEN MONTH(@StartDate) = 1
168 THEN 'Jan'
169 WHEN MONTH(@StartDate) = 2
170 THEN 'Feb'
171 WHEN MONTH(@StartDate) = 3
172 THEN 'Mar'
173 WHEN MONTH(@StartDate) = 4
174 THEN 'Apr'
175 WHEN MONTH(@StartDate) = 5
176 THEN 'May'
177 WHEN MONTH(@StartDate) = 6
178 THEN 'Jun'
179 WHEN MONTH(@StartDate) = 7
180 THEN 'Jul'
181 WHEN MONTH(@StartDate) = 8
182 THEN 'Aug'
183 WHEN MONTH(@StartDate) = 9
184 THEN 'Sep'
185 WHEN MONTH(@StartDate) = 10
186 THEN 'Oct'
187 WHEN MONTH(@StartDate) = 11
188 THEN 'Nov'
189 WHEN MONTH(@StartDate) = 12
190 THEN 'Dec'
191 END AS varchar
192) AS CALENDAR_MONTH_ABBR,
193
194CAST(MONTH(@StartDate) AS int) AS CALENDAR_MONTH,
195
196CONCAT(
197CAST
198(
199 CASE
200 WHEN MONTH(@StartDate) = 1
201 THEN 'Jan'
202 WHEN MONTH(@StartDate) = 2
203 THEN 'Feb'
204 WHEN MONTH(@StartDate) = 3
205 THEN 'Mar'
206 WHEN MONTH(@StartDate) = 4
207 THEN 'Apr'
208 WHEN MONTH(@StartDate) = 5
209 THEN 'May'
210 WHEN MONTH(@StartDate) = 6
211 THEN 'Jun'
212 WHEN MONTH(@StartDate) = 7
213 THEN 'Jul'
214 WHEN MONTH(@StartDate) = 8
215 THEN 'Aug'
216 WHEN MONTH(@StartDate) = 9
217 THEN 'Sep'
218 WHEN MONTH(@StartDate) = 10
219 THEN 'Oct'
220 WHEN MONTH(@StartDate) = 11
221 THEN 'Nov'
222 WHEN MONTH(@StartDate) = 12
223 THEN 'Dec'
224 END AS VARCHAR
225) ,' - ',CAST(YEAR(@StartDate) AS int) ) AS CALENDAR_YEAR_MONTH,
226
227DATEPART( wk, @StartDate) AS CALENDAR_WEEK_OF_YEAR,
228
229CAST(DATENAME(DW,@StartDate) AS varchar) AS CALENDAR_DAY_NAME,
230
231CAST(
232 CASE
233 WHEN DATENAME(DW,@StartDate) = 'Monday'
234 THEN 'Mon'
235 WHEN DATENAME(DW,@StartDate) = 'Tuesday'
236 THEN 'Tue'
237 WHEN DATENAME(DW,@StartDate) = 'Wednesday'
238 THEN 'Wed'
239 WHEN DATENAME(DW,@StartDate) = 'Thursday'
240 THEN 'Thurs'
241 WHEN DATENAME(DW,@StartDate) = 'Friday'
242 THEN 'Fri'
243 WHEN DATENAME(DW,@StartDate) = 'Saturday'
244 THEN 'Sat'
245 WHEN DATENAME(DW,@StartDate) = 'Sunday'
246 THEN 'Sun'
247 END AS varchar
248 ) AS CALENDAR_DAY_NAME_ABBR,
249
250CAST(DAY(@StartDate) AS INT) AS CALENDAR_DAY,
251
252CAST(
253 CASE
254 WHEN DAY(@StartDate) in (1,21,31)
255 THEN CONVERT(varchar,DAY(@StartDate)) + 'st'
256 WHEN DAY(@StartDate) IN (2,22)
257 THEN CONVERT(varchar,DAY(@StartDate)) + 'nd'
258 WHEN DAY(@StartDate) IN (3,23)
259 THEN CONVERT(varchar,DAY(@StartDate)) + 'rd'
260 ELSE convert(varchar,DAY(@StartDate)) + 'th '
261 END AS varchar
262 ) AS CALENDAR_DAY_SUFFIX,
263
264
265CAST(YEAR(DATEADD(MONTH, -3, @StartDate))+1 AS int) AS ACCOUNTING_YEAR,
266
267CAST
268(
269 CASE
270 WHEN MONTH(@StartDate) IN (4, 5, 6)
271 THEN '1'
272 WHEN MONTH(@StartDate) IN (7, 8, 9)
273 THEN '2'
274 WHEN MONTH(@StartDate) IN (10, 11, 12)
275 THEN '3'
276 WHEN MONTH(@StartDate) IN (1,2,3)
277 THEN '4'
278 END AS int
279) AS ACCOUNTING_QUARTER,
280
281CAST
282(
283 CASE
284 WHEN MONTH(@StartDate) IN (4, 5, 6)
285 THEN 'Q1'
286 WHEN MONTH(@StartDate) IN (7, 8, 9)
287 THEN 'Q2'
288 WHEN MONTH(@StartDate) IN (10, 11, 12)
289 THEN 'Q3'
290 WHEN MONTH(@StartDate) IN (1,2,3)
291 THEN 'Q4'
292 END AS VARCHAR) AS ACCOUNTING_QUARTER_ABBR,
293
294CAST
295(
296 CASE
297 WHEN MONTH(@StartDate) IN (4, 5, 6)
298 THEN 'First'
299 WHEN MONTH(@StartDate) IN (7, 8, 9)
300 THEN 'Second'
301 WHEN MONTH(@StartDate) IN (10, 11, 12)
302 THEN 'Third'
303 WHEN MONTH(@StartDate) IN (1, 2, 3)
304 THEN 'Fourth'
305 END AS VARCHAR
306) AS ACCOUNTING_QUARTER_NAME,
307
308CAST(DATENAME(MM,@StartDate) AS varchar )AS ACCOUNTING_MONTH_NAME,
309
310CAST
311(
312 CASE
313 WHEN MONTH(@StartDate) = 1
314 THEN 'Jan'
315 WHEN MONTH(@StartDate) = 2
316 THEN 'Feb'
317 WHEN MONTH(@StartDate) = 3
318 THEN 'Mar'
319 WHEN MONTH(@StartDate) = 4
320 THEN 'Apr'
321 WHEN MONTH(@StartDate) = 5
322 THEN 'May'
323 WHEN MONTH(@StartDate) = 6
324 THEN 'Jun'
325 WHEN MONTH(@StartDate) = 7
326 THEN 'Jul'
327 WHEN MONTH(@StartDate) = 8
328 THEN 'Aug'
329 WHEN MONTH(@StartDate) = 9
330 THEN 'Sep'
331 WHEN MONTH(@StartDate) = 10
332 THEN 'Oct'
333 WHEN MONTH(@StartDate) = 11
334 THEN 'Nov'
335 WHEN MONTH(@StartDate) = 12
336 THEN 'Dec'
337 END AS VARCHAR
338) AS ACCOUNTING_MONTH_ABBR,
339
340MONTH(DATEADD(MONTH, -3, @StartDate)) + 0 AS ACCOUNTING_MONTH,
341
342CONCAT(
343CAST
344(
345 CASE
346 WHEN MONTH(@StartDate) = 1
347 THEN 'Jan'
348 WHEN MONTH(@StartDate) = 2
349 THEN 'Feb'
350 WHEN MONTH(@StartDate) = 3
351 THEN 'Mar'
352 WHEN MONTH(@StartDate) = 4
353 THEN 'Apr'
354 WHEN MONTH(@StartDate) = 5
355 THEN 'May'
356 WHEN MONTH(@StartDate) = 6
357 THEN 'Jun'
358 WHEN MONTH(@StartDate) = 7
359 THEN 'Jul'
360 WHEN MONTH(@StartDate) = 8
361 THEN 'Aug'
362 WHEN MONTH(@StartDate) = 9
363 THEN 'Sep'
364 WHEN MONTH(@StartDate) = 10
365 THEN 'Oct'
366 WHEN MONTH(@StartDate) = 11
367 THEN 'Nov'
368 WHEN MONTH(@StartDate) = 12
369 THEN 'Dec'
370 END AS VARCHAR
371) ,' - ',CAST(YEAR(@StartDate) AS int) ) AS ACCOUNTING_YEAR_MONTH,
372
373 CASE
374 WHEN CAST(DATEDIFF(WEEK, DATEADD(YEAR, DATEDIFF(YEAR, 0, CAST(CONCAT(YEAR(DATEADD(month, -3, @StartDate)),'-04-01')AS date) ), 0), @StartDate) -13 AS int) <0
375 THEN '0'
376 ELSE CAST(DATEDIFF(WEEK, DATEADD(YEAR, DATEDIFF(YEAR, 0, cast(concat(year(dateadd(month, -3, @StartDate)),'-04-01')AS date) ), 0), @StartDate) -13 AS int)
377 END AS ACCOUNTING_WEEK_OF_YEAR,
378
379CAST(DATENAME(DW,@StartDate) AS VARCHAR) AS ACCOUNTING_DAY_NAME,
380
381CAST
382(
383 CASE
384 WHEN DATENAME(DW,@StartDate) = 'Monday'
385 THEN 'Mon'
386 WHEN DATENAME(DW,@StartDate) = 'Tuesday'
387 THEN 'Tue'
388 WHEN DATENAME(DW,@StartDate) = 'Wednesday'
389 THEN 'Wed'
390 WHEN DATENAME(DW,@StartDate) = 'Thursday'
391 THEN 'Thurs'
392 WHEN DATENAME(DW,@StartDate) = 'Friday'
393 THEN 'Fri'
394 WHEN DATENAME(DW,@StartDate) = 'Saturday'
395 THEN 'Sat'
396 WHEN DATENAME(DW,@StartDate) = 'Sunday'
397 THEN 'Sun'
398 END AS varchar) AS ACCOUNTING_DAY_NAME_ABBR,
399
400CAST(DAY(@StartDate) AS int) AS ACCOUNTING_DAY,
401
402CAST
403(
404 CASE
405 WHEN DAY(@StartDate) IN (1,21,31)
406 THEN CONVERT(varchar,DAY(@StartDate)) + 'st'
407 WHEN DAY(@StartDate) IN (2,22)
408 THEN CONVERT(varchar,DAY(@StartDate)) + 'nd'
409 WHEN DAY(@StartDate) IN (3,23)
410 THEN CONVERT(varchar,DAY(@StartDate)) + 'rd'
411 ELSE convert(varchar,DAY(@StartDate)) + 'th '
412 END AS VARCHAR
413) AS ACCOUNTING_DAY_SUFFIX,
414
415CAST
416(
417 CASE
418 WHEN DATENAME(DW,@StartDate) = 'Monday'
419 THEN '1'
420 WHEN DATENAME(DW,@StartDate) = 'Tuesday'
421 THEN '2'
422 WHEN DATENAME(DW,@StartDate) = 'Wednesday'
423 THEN '3'
424 WHEN DATENAME(DW,@StartDate) = 'Thursday'
425 THEN '4'
426 WHEN DATENAME(DW,@StartDate) = 'Friday'
427 THEN '5'
428 WHEN DATENAME(DW,@StartDate) = 'Saturday'
429 THEN '6'
430 WHEN DATENAME(DW,@StartDate) = 'Sunday'
431 THEN '7'
432 END AS int
433) AS DAY_OF_WEEK,
434
435
436CAST(DATEPART(dayofyear, @StartDate) AS int) AS DAY_OF_YEAR,
437
438CAST(day(eomonth(@STARTDATE)) AS int) AS DAYS_IN_MONTH,
439
440CAST(DATEDIFF(DAY, @StartDate, (DATEADD(month, DATEDIFF(month, -1, @StartDate), -1))) AS INT)AS MONTH_DAYS_REMAINING,
441
442CONCAT(CAST(YEAR(@StartDate) AS INT),CAST(DATEDIFF(WEEK, DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate), 0), @StartDate) +1 AS INT)) AS YEAR_WEEK,
443
444
445cast(DATEADD(wk, DATEDIFF(d, 0, @StartDate) / 7, 0)as date) AS FIRST_DAY_IN_WEEK,
446
447CAST(DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDate), DATEDIFF(dd, 0, @StartDate))) AS DATE ) AS LAST_DAY_IN_WEEK,
448
449CAST(DATEADD(month, DATEDIFF(month, 0, @StartDate), 0) AS DATE) AS FIRST_DAY_IN_MONTH,
450
451CAST(DATEADD(month, DATEDIFF(month, -1, @StartDate), -1) AS DATE) AS LAST_DAY_IN_MONTH,
452
453CAST
454(
455 CASE
456 WHEN DATENAME(DW,(DATEADD(month, DATEDIFF(month, -1, @StartDate), -1))) = 'SATURDAY'
457 THEN (DATEADD(month, DATEDIFF(month, -1, @StartDate), -2))
458 ELSE CASE WHEN DATENAME(DW,(DATEADD(month, DATEDIFF(month, -1, @StartDate), -1))) = 'SUNDAY'
459 THEN (DATEADD(month, DATEDIFF(month, -1, @StartDate), -4))
460 ELSE (DATEADD(month, DATEDIFF(month, -1, @StartDate), -1))
461 END
462 END AS date
463) AS LAST_WORKING_DAY,
464
465CAST(DATEADD(qq,DATEDIFF(qq,0,@StartDate),0) AS DATE) as FIRST_DAY_IN_QUARTER,
466
467CAST(DATEADD(qq,DATEDIFF(qq,-1,@StartDate),-1) AS DATE) as LAST_DAY_IN_QUARTER,
468
469CAST(DATEADD (D, -1 * DatePart (DW, @StartDate) + 7, @StartDate)AS DATE) AS WEEK_ENDING_SATURDAY,
470
471CAST(DATEADD(DAY, 1 - DATEPART(WEEKDAY, @StartDate), CAST(@StartDate AS DATE))AS DATE) AS WEEK_BEGINNING_SUNDAY
472
473
474SET @StartDate = DATEADD(dd, 1, @StartDate)
475END
476END
477
478GO