stVjmqLD

1IF (NOT EXISTS (SELECT * 
2                 FROM INFORMATION_SCHEMA.TABLES 
3                 WHERE TABLE_SCHEMA = 'dbo' 
4                 AND  TABLE_NAME = 'Dim_Calendar'))
5BEGIN
6
7DECLARE @StartDate DATE = '2014-01-01';
8DECLARE @years INT = 15;
9
10DECLARE @CutoffDate DATE = DATEADD(DAY, -1, DATEADD(YEAR, @years, @StartDate));
11
12DECLARE @values TABLE(Date Date, Day int, DayName varchar(10), Week int, ISOWeek int, DayOfWeek int, Month int, MonthName varchar(12), Quarter int, Year int, FirstOfMonth date, LastOfYear date, DayOfYear int);
13
14;WITH seq(n) AS 
15(
16  SELECT 0 UNION ALL SELECT n + 1 FROM seq
17  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
18),
19d(d) AS 
20(
21  SELECT DATEADD(DAY, n, @StartDate) FROM seq
22),
23src AS
24(
25  SELECT
26    [Date]         = CONVERT(date, d),
27    [Day]          = DATEPART(DAY,       d),
28    [DayName]      = DATENAME(WEEKDAY,   d),
29    [Week]         = DATEPART(WEEK,      d),
30    [ISOWeek]      = DATEPART(ISO_WEEK,  d),
31    [DayOfWeek]    = DATEPART(WEEKDAY,   d),
32    [Month]        = DATEPART(MONTH,     d),
33    [MonthName]    = DATENAME(MONTH,     d),
34    [Quarter]      = DATEPART(Quarter,   d),
35    [Year]         = DATEPART(YEAR,      d),
36    [FirstOfMonth] = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
37    [LastOfYear]   = DATEFROMPARTS(YEAR(d), 12, 31),
38    [DayOfYear]    = DATEPART(DAYOFYEAR, d)
39  FROM d
40)
41
42SELECT * INTO Dim_Calendar FROM src
43  ORDER BY [Date]
44  OPTION (MAXRECURSION 0);
45
46
47ALTER TABLE dbo.Dim_Calendar
48ALTER COLUMN [Date] Date NOT NULL;
49
50--create index
51CREATE UNIQUE CLUSTERED INDEX PK_DateDimension ON dbo.Dim_Calendar([Date]);
52
53ALTER TABLE dbo.Dim_Calendar ADD CONSTRAINT
54	PK_Dim_Calendar PRIMARY KEY NONCLUSTERED ([Date])
55
56--create relations
57ALTER TABLE dbo.Fact_Shipment
58ADD FOREIGN KEY (DepartmentDate) REFERENCES Dim_Calendar([Date])
59
60ALTER TABLE dbo.Fact_Shipment
61ADD FOREIGN KEY (ArrivalDate) REFERENCES Dim_Calendar([Date])
62
63ALTER TABLE dbo.Fact_Shipment
64ADD FOREIGN KEY (VoyageDepartmentDate) REFERENCES Dim_Calendar([Date])
65
66ALTER TABLE dbo.Fact_TimeInPort
67ADD FOREIGN KEY (Arrival) REFERENCES Dim_Calendar([Date])
68
69ALTER TABLE dbo.Fact_TimeInPort
70ADD FOREIGN KEY (Department) REFERENCES Dim_Calendar([Date])
71
72END