Datumsdimension aufbauen

---für dieses Beispiel nehmen wir unsere Core Datenbank aus dem DWH und bauen uns eine Datumsdimension auf.
USE [DWH_Core];

IF OBJECT_ID('dbo.[Dim_Date]', 'U') IS NOT NULL 
 DROP TABLE dbo.[Dim_Date]; 

CREATE TABLE [dbo].[Dim_Date](
	[DateID] [int] NULL,
	[Date_DE] [char](10) NULL,
	[Date_US] [char](10) NULL,
	[Date_ANSI] [char](10) NULL,
	[Date_ISO8601] [char](10) NULL,
	[YearID] [int] NULL,
	[MonthID] [nvarchar](10) NULL,
	[QuarterID] [char](6) NULL,
	[KwID] [char](6) NULL,
	[DayofYear] [int] NULL,
	[CountofDayMonth] [int] NULL,
	[restofDayYear] [int] NULL,
	[DayofWeek] [int] NULL,
	[CalendarWeek] [int] NULL,
	[Month] [int] NULL,
	[Day] [int] NULL,
	[Quarter] [int] NULL,
	[beginLastWeek] [date] NULL,
	[endLastWeek] [date] NULL,
	[beginCurrentWeek] [date] NULL,
	[endCurrentWeek] [date] NULL,
	[beginNextWeek] [date] NULL,
	[endNextWeek] [date] NULL,
	[beginLastMonth] [date] NULL,
	[endLastMonth] [date] NULL,
	[beginCurrentMonth] [date] NULL,
	[endCurrentMonth] [date] NULL,
	[beginNextMonth] [date] NULL,
	[endNextMonth] [date] NULL,
	[beginLastYear] [date] NULL,
	[endLastYear] [date] NULL,
	[beginCurrentYear] [date] NULL,
	[endCurrentYear] [date] NULL,
	[beginNextYear] [date] NULL,
	[endNextYear] [date] NULL,
	[weekday_DE] [nvarchar](30) NULL,
	[MonthName_DE] [nvarchar](30) NULL
) 
GO
DECLARE @StartDate DATE, @EndDate DATE

SET @StartDate = '1900-01-01'
SET @EndDate = '2099-12-31';

WITH DateRange(DateData) AS 
(
  SELECT @StartDate as Date
  UNION ALL
  SELECT DATEADD(d,1,DateData)
  FROM DateRange 
  WHERE DateData < @EndDate
)
Insert into [dbo].[Dim_Date]
SELECT 
 CONVERT(INT,CONVERT(VARCHAR(8),DateData,112)) AS [DateID]
	 ,CONVERT(char(10), DateData, 104) AS [Date_DE]
	 ,CONVERT(char(10), DateData, 101) AS [Date_US] 
	 ,CONVERT(char(10), DateData, 102) AS [Date_ANSI] 
	 ,CONVERT(char(10), DateData, 23) AS [Date_ISO8601] 
	 ,DATEPART(year, DateData) AS [YearID]
	 ,FORMAT(DateData,'yyyyMM') AS [MonthID]
	 ,CAST(year(DateData) AS char(4)) + CAST(DATEPART( QUARTER, DateData ) AS char(2)) AS [QuarterID] 
	 ,CAST(year(DateData) AS char(4)) + CAST(DATEPART( wk, DateData ) AS char(2)) AS [KwID]
	 ,DATEPART(dayofyear, DateData) AS [DayofYear]
	 ,DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,DateData),0))) AS [CountofDayMonth]
	 ,DATEDIFF(day, DateData, DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)))) [restofDayYear]
	 ,DATEPART(WEEKDAY, DateData) AS [DayofWeek]
	 ,DATEPART( wk, DateData ) AS [CalendarWeek]
	 ,DATEPART(MONTH, DateData) AS [Month]
	 ,DATEPART(DAY, DateData) AS [Day]
	 ,DATEPART( QUARTER, DateData ) AS [Quarter]
	 ,CAST(DATEADD(WEEK, -1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS [beginLastWeek]
	 ,CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS [endLastWeek]
	 ,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData) AS DATE) AS [beginCurrentWeek]
	 ,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData) AS DATE) AS [endCurrentWeek]
	 ,CAST(DATEADD(WEEK, 1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS [beginNextWeek]
	 ,CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS [endNextWeek]
	 ,CAST(DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS [beginLastMonth]
	 ,CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS [endLastMonth]
	 ,DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1) AS [beginCurrentMonth]
	 ,DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1))) AS [endCurrentMonth]
	 ,DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1)) AS [beginNextMonth]
	 ,DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1))) AS [endNextMonth]
	 ,DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS [beginLastYear]
	 ,DATEADD(DAY, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS [endLastYear]
	 ,DATEFROMPARTS(YEAR(DateData), 1, 1) AS [beginCurrentYear]
	 ,DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS [endCurrentYear]
	 ,DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS [beginNextYear]
	 ,DATEADD(DAY, -1, DATEADD(YEAR, 2, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS [endNextYear]
	 ,DATENAME(dw,DateData) AS [weekday_DE]
	 ,DATENAME(MONTH,DateData) AS [MonthName_DE]
FROM DateRange
OPTION (MAXRECURSION 0);															
																
																
																
																

ALTER TABLE [dbo].[Dim_Date] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)