---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
)
Hinterlasse jetzt einen Kommentar