· 6 years ago · Jul 16, 2019, 10:32 PM
1DECLARE @StartDate DATE = '20160101',
2@NumberOfYears INT = 4;
3
4-- prevent set or regional settings from interfering with
5-- interpretation of dates / literals
6
7SET DATEFIRST 1;
8SET DATEFORMAT mdy;
9SET LANGUAGE US_ENGLISH;
10
11DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
12
13-- this is just a holding table for intermediate calculations:
14
15CREATE TABLE #dim
16(
17 [date] DATE PRIMARY KEY,
18 [day] AS DATEPART(DAY, [date]),
19 [month] AS DATEPART(MONTH, [date]),
20 FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
21 [MonthName] AS DATENAME(MONTH, [date]),
22 [week] AS DATEPART(WEEK, [date]),
23 [ISOweek] AS DATEPART(ISO_WEEK, [date]),
24 [DayOfWeek] AS DATEPART(WEEKDAY, [date]),
25 [quarter] AS DATEPART(QUARTER, [date]),
26 [year] AS DATEPART(YEAR, [date]),
27 FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
28 Style112 AS CONVERT(CHAR(8), [date], 112),
29 Style101 AS CONVERT(CHAR(10), [date], 101)
30);
31
32-- use the catalog views to generate as many rows as we need
33
34INSERT #dim([date])
35SELECT d
36FROM
37(
38 SELECT d = DATEADD(DAY, rn - 1, @StartDate)
39 FROM
40 (
41 SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
42 rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
43 FROM sys.all_objects AS s1
44 CROSS JOIN sys.all_objects AS s2
45 -- on my system this would support > 5 million days
46 ORDER BY s1.[object_id]
47 ) AS x
48) AS y;
49
50go
51
52
53DROP table IF EXISTS dbo.DateDimension
54
55go
56
57CREATE TABLE dbo.DateDimension
58(
59 --DateKey INT NOT NULL PRIMARY KEY,
60 [Date] DATE NOT NULL,
61 [Day] TINYINT NOT NULL,
62 [Weekday] TINYINT NOT NULL,
63 WeekDayName VARCHAR(10) NOT NULL,
64 IsWeekend BIT NOT NULL,
65 [DayOfYear] SMALLINT NOT NULL,
66 WeekOfMonth TINYINT NOT NULL,
67 WeekOfYear TINYINT NOT NULL,
68 [Month] TINYINT NOT NULL,
69 [MonthName] VARCHAR(10) NOT NULL,
70 [Quarter] TINYINT NOT NULL,
71 QuarterName VARCHAR(6) NOT NULL,
72 [Year] INT NOT NULL,
73 [Year Text] VARCHAR(20) NOT NULL,
74 [MonthYear] VARCHAR(20) NOT NULL,
75 FirstDayOfMonth DATE NOT NULL,
76 LastDayOfMonth DATE NOT NULL,
77 FirstDayOfQuarter DATE NOT NULL,
78 LastDayOfQuarter DATE NOT NULL,
79 FirstDayOfYear DATE NOT NULL,
80 LastDayOfYear DATE NOT NULL,
81 FirstDayOfNextMonth DATE NOT NULL,
82 FirstDayOfNextYear DATE NOT NULL
83);
84GO
85
86-- create other useful index(es) here
87
88
89INSERT dbo.DateDimension WITH (TABLOCKX)
90SELECT
91 --DateKey = CONVERT(INT, Style112),
92 [Date] = [date],
93 [Day] = CONVERT(TINYINT, [day]),
94 [Weekday] = CONVERT(TINYINT, [DayOfWeek]),
95 [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
96 [IsWeekend] = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
97 [DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
98 WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER
99 (PARTITION BY [year], [month] ORDER BY [week])),
100 WeekOfYear = CONVERT(TINYINT, [week]),
101 [Month] = CONVERT(TINYINT, [month]),
102 [MonthName] = CONVERT(VARCHAR(10), [MonthName]),
103 [Quarter] = CONVERT(TINYINT, [quarter]),
104 QuarterName = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First'
105 WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),
106 [Year] = [year],
107 [Year Text] = CAST([year] as varchar(20)),
108 MonthYear = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
109 FirstDayOfMonth = FirstOfMonth,
110 LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
111 FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
112 LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
113 FirstDayOfYear = FirstOfYear,
114 LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
115 FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
116 FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
117FROM #dim
118OPTION (MAXDOP 1);
119
120
121drop table #dim