recreate D_Datum
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET LANGUAGE ‘German’
GO
SET DATEFIRST 1
GO
IF EXISTS(
SELECT * FROM sys.objects
WHERE name = ‘D_Datum’)
DROP TABLE D_Datum
GO
CREATE TABLE D_Datum(
[Datum] date NOT NULL PRIMARY KEY,
[Jahr (#)] int NOT NULL,
[Quartal (#)] tinyint NOT NULL,
[Quartal (Bezeichnung)] nvarchar(3) NOT NULL,
[Monat (#)] tinyint NOT NULL,
[Monat (Bezeichnung)] nvarchar(12) NOT NULL,
[Kalenderwoche (#)] tinyint NOT NULL,
[Tag im Monat (#)] tinyint NOT NULL,
[Tag in der Woche (#)] tinyint NOT NULL,
[Tag (Bezeichnung)] nvarchar(12) NOT NULL,
[Year-to-Date | YTD(1/0)] tinyint NOT NULL,
[Year-to-Date_ZV | YTD(1/0)] tinyint NOT NULL,
[Month-to-Date | MTD(1/0)] tinyint NOT NULL,
[Month-to-Date_ZV | MTD(1/0)] tinyint NOT NULL,
[Wochenende (1/0)] tinyint NOT NULL,
[Jahresbezug (#)] int NOT NULL,
[Quartalsbezug (#)] int NOT NULL,
[Monatsbezug (#)] int NOT NULL,
[Wochenbezug (#)] int NOT NULL,
[Tagesbezug (#)] int NOT NULL,
[Jahr – Monat (#)] nvarchar(7) NOT NULL,
[Jahr – Quartal (#)] nvarchar(7) NOT NULL,
[Letzter Tag des Monats (1/0)] tinyint NOT NULL
)
GO
DECLARE @FullDate date
DECLARE @DayofWeek tinyint
DECLARE @DayName nvarchar(11)
DECLARE @DayNameAbbrev nvarchar(2)
DECLARE @week_begin date
DECLARE @DayofMonth tinyint
DECLARE @MonthName nvarchar(11)
DECLARE @MonthNameAbbrev nvarchar(3)
DECLARE @CalWEEK tinyint
DECLARE @CalMONTH int
DECLARE @CalQUARTER tinyint
DECLARE @CalQUARTERLabel nvarchar(3)
DECLARE @CalYEAR int
DECLARE @IsWeekend BIT
DECLARE @relDay int
DECLARE @relWeek int
DECLARE @relMonth int
DECLARE @relQuarter int
DECLARE @relYear int
DECLARE @ytd bit
DECLARE @ytdzv bit
DECLARE @mtd bit
DECLARE @mtdzv bit
DECLARE @year_quarter nvarchar(7)
DECLARE @year_month nvarchar(7)
DECLARE @year_week nvarchar(7)
DECLARE @ldom bit
DECLARE @mtd_year tinyint
SELECT @mtd_year = 0
–Start Datum
SELECT @FullDate = ‘2014-01-01’
–Ende Datum
WHILE (@FullDate <= ‘2030-12-31’)
BEGIN
SELECT @DayofWeek = DATEPART (DW , @FullDate)
SELECT @DayName = DATENAME(Weekday,@FullDate)
SELECT @DayNameAbbrev = LEFT(DATENAME(Weekday,@FullDate), 2)
–SELECT @week_begin = DATEADD(dd, -(DATEPART(dw, @FullDate) – 1), @FullDate)
SELECT @DayofMonth = DATEPART (DAY, @FullDate)
SELECT @MonthName = DATENAME(MONTH,@FullDate)
SELECT @MonthNameAbbrev = LEFT(DATENAME(MONTH,@FullDate), 3)
SELECT @CalYEAR = DATEPART (YEAR, @FullDate)
SELECT @CalQUARTER = DATEPART (QUARTER, @FullDate)
SELECT @CalQUARTERLabel = ‘Q ‘ + CAST(@CalQUARTER AS nvarchar(2))
SELECT @CalMONTH = DATEPART (MONTH , @FullDate)
SELECT @CalWEEK = DATEPART (ISO_WEEK , @FullDate)
SELECT @year_quarter = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalQUARTER AS nvarchar(2))), 2)
SELECT @year_month = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalMONTH AS nvarchar(2))), 2)
SELECT @year_week = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalWEEK AS nvarchar(2))), 2)
IF ( @DayofWeek = 6 OR @DayofWeek = 7 )
SELECT @IsWeekend = 1
ELSE
SELECT @IsWeekend = 0
SELECT @relDay = DATEDIFF(DAY, CURRENT_TIMESTAMP, @FullDate)
SELECT @relWeek = -1 * DATEDIFF(ww, DATEADD(dd,-@@datefirst,@FullDate), DATEADD(dd,-@@datefirst,CURRENT_TIMESTAMP))
SELECT @relMonth = DATEDIFF(MONTH, CURRENT_TIMESTAMP, @FullDate)
SELECT @relQuarter = DATEDIFF(QUARTER, CURRENT_TIMESTAMP, @FullDate)
SELECT @relYear = DATEDIFF(YEAR, CURRENT_TIMESTAMP, @FullDate)
IF ( @DayofMonth < DATEPART(DAY, CURRENT_TIMESTAMP) )
SELECT @mtd = 1
ELSE
SELECT @mtd = 0
IF ( @mtd = 1 AND @CalYEAR = YEAR(CURRENT_TIMESTAMP) )
SELECT @mtdzv = 1
ELSE
SELECT @mtdzv = 0
IF ( @FullDate < DATEFROMPARTS(@CalYEAR, MONTH(CURRENT_TIMESTAMP), DAY(CURRENT_TIMESTAMP)) )
SELECT @ytd = 1
ELSE
SELECT @ytd = 0
/*A.Zausch: 240229: Anp. für den 29.02.
IF ( @FullDate < DATEFROMPARTS(@CalYEAR, MONTH(CURRENT_TIMESTAMP), DAY(CURRENT_TIMESTAMP)-1) )
SELECT @ytd = 1
ELSE
SELECT @ytd = 0
*/
/*
A.Zausch: Überarbeitet 04.10.2016
IF ( @FullDate >= DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AND @FullDate < DATEADD(DAY, -1, DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), CURRENT_TIMESTAMP)) )
SELECT @ytdzv = 1
ELSE
SELECT @ytdzv = 0
*/
IF ( @FullDate >= DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR),year(CURRENT_TIMESTAMP)
–, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)
)
AND @FullDate < DATEADD(DAY, -1, DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), CURRENT_TIMESTAMP))
)
SELECT @ytdzv = 1
ELSE
SELECT @ytdzv = 0
IF (@FullDate = EOMONTH(@FullDate))
SELECT @ldom = 1
ELSE
SELECT @ldom = 0
INSERT D_Datum(
[Datum],
[Jahr (#)],
[Quartal (#)],
[Quartal (Bezeichnung)],
[Monat (#)],
[Monat (Bezeichnung)],
[Kalenderwoche (#)],
[Tag im Monat (#)],
[Tag in der Woche (#)],
[Tag (Bezeichnung)],
[Year-to-Date | YTD(1/0)],
[Year-to-Date_ZV | YTD(1/0)],
[Month-to-Date | MTD(1/0)],
[Month-to-Date_ZV | MTD(1/0)],
[Wochenende (1/0)],
[Jahresbezug (#)],
[Quartalsbezug (#)],
[Monatsbezug (#)],
[Wochenbezug (#)],
[Tagesbezug (#)],
[Jahr – Monat (#)],
[Jahr – Quartal (#)],
[Letzter Tag des Monats (1/0)]
)VALUES(
@FullDate,
@CalYEAR,
@CalQUARTER,
@CalQUARTERLabel,
@CalMONTH,
@MonthName,
@CalWEEK,
@DayofMonth,
@DayofWeek,
@DayName,
@ytd,
@ytdzv,
@mtd,
@mtdzv,
@IsWeekend,
@relYear,
@relQuarter,
@relMonth,
@relWeek,
@relDay,
@year_month,
@year_quarter,
@ldom
)
SELECT @FullDate = DATEADD(DAY, 1, @Fulldate)
END
WHILE ( @mtd_year <= DATEDIFF(YEAR, ‘2011-01-01’, CURRENT_TIMESTAMP) )
BEGIN
UPDATE D_Datum SET [Month-to-Date_ZV | MTD(1/0)] = 1 WHERE Datum IN ( SELECT DATEADD(WEEK, -52 * @mtd_year, Datum) FROM D_Datum WHERE Datum >= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1) AND [Month-to-Date_ZV | MTD(1/0)] = 1 )
SELECT @mtd_year = @mtd_year + 1
END
GO
UPDATE D_Datum SET [Year-to-Date_ZV | YTD(1/0)] = 0 WHERE
–kein Schaltjahr
–[Monatsbezug (#)]=-12 AND [Tag im Monat (#)] =1
–Schaltjahr
[Monatsbezug (#)]=-12 AND [Tag im Monat (#)] IN (1,2)
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET LANGUAGE ‘German’
GO
SET DATEFIRST 1
GO
IF EXISTS(
SELECT * FROM sys.objects
WHERE name = ‘D_Datum’)
DROP TABLE D_Datum
GO
CREATE TABLE D_Datum(
[Datum] date NOT NULL PRIMARY KEY,
[Jahr (#)] int NOT NULL,
[Quartal (#)] tinyint NOT NULL,
[Quartal (Bezeichnung)] nvarchar(3) NOT NULL,
[Monat (#)] tinyint NOT NULL,
[Monat (Bezeichnung)] nvarchar(12) NOT NULL,
[Kalenderwoche (#)] tinyint NOT NULL,
[Tag im Monat (#)] tinyint NOT NULL,
[Tag in der Woche (#)] tinyint NOT NULL,
[Tag (Bezeichnung)] nvarchar(12) NOT NULL,
[Year-to-Date | YTD(1/0)] tinyint NOT NULL,
[Year-to-Date_ZV | YTD(1/0)] tinyint NOT NULL,
[Month-to-Date | MTD(1/0)] tinyint NOT NULL,
[Month-to-Date_ZV | MTD(1/0)] tinyint NOT NULL,
[Wochenende (1/0)] tinyint NOT NULL,
[Jahresbezug (#)] int NOT NULL,
[Quartalsbezug (#)] int NOT NULL,
[Monatsbezug (#)] int NOT NULL,
[Wochenbezug (#)] int NOT NULL,
[Tagesbezug (#)] int NOT NULL,
[Jahr – Monat (#)] nvarchar(7) NOT NULL,
[Jahr – Quartal (#)] nvarchar(7) NOT NULL,
[Letzter Tag des Monats (1/0)] tinyint NOT NULL
)
GO
DECLARE @FullDate date
DECLARE @DayofWeek tinyint
DECLARE @DayName nvarchar(11)
DECLARE @DayNameAbbrev nvarchar(2)
DECLARE @week_begin date
DECLARE @DayofMonth tinyint
DECLARE @MonthName nvarchar(11)
DECLARE @MonthNameAbbrev nvarchar(3)
DECLARE @CalWEEK tinyint
DECLARE @CalMONTH int
DECLARE @CalQUARTER tinyint
DECLARE @CalQUARTERLabel nvarchar(3)
DECLARE @CalYEAR int
DECLARE @IsWeekend BIT
DECLARE @relDay int
DECLARE @relWeek int
DECLARE @relMonth int
DECLARE @relQuarter int
DECLARE @relYear int
DECLARE @ytd bit
DECLARE @ytdzv bit
DECLARE @mtd bit
DECLARE @mtdzv bit
DECLARE @year_quarter nvarchar(7)
DECLARE @year_month nvarchar(7)
DECLARE @year_week nvarchar(7)
DECLARE @ldom bit
DECLARE @mtd_year tinyint
SELECT @mtd_year = 0
–Start Datum
SELECT @FullDate = ‘2014-01-01’
–Ende Datum
WHILE (@FullDate <= ‘2030-12-31’)
BEGIN
SELECT @DayofWeek = DATEPART (DW , @FullDate)
SELECT @DayName = DATENAME(Weekday,@FullDate)
SELECT @DayNameAbbrev = LEFT(DATENAME(Weekday,@FullDate), 2)
–SELECT @week_begin = DATEADD(dd, -(DATEPART(dw, @FullDate) – 1), @FullDate)
SELECT @DayofMonth = DATEPART (DAY, @FullDate)
SELECT @MonthName = DATENAME(MONTH,@FullDate)
SELECT @MonthNameAbbrev = LEFT(DATENAME(MONTH,@FullDate), 3)
SELECT @CalYEAR = DATEPART (YEAR, @FullDate)
SELECT @CalQUARTER = DATEPART (QUARTER, @FullDate)
SELECT @CalQUARTERLabel = ‘Q ‘ + CAST(@CalQUARTER AS nvarchar(2))
SELECT @CalMONTH = DATEPART (MONTH , @FullDate)
SELECT @CalWEEK = DATEPART (ISO_WEEK , @FullDate)
SELECT @year_quarter = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalQUARTER AS nvarchar(2))), 2)
SELECT @year_month = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalMONTH AS nvarchar(2))), 2)
SELECT @year_week = (CAST(@CalYEAR AS nvarchar(4))) + ‘-‘ + RIGHT(‘0’ + (CAST(@CalWEEK AS nvarchar(2))), 2)
IF ( @DayofWeek = 6 OR @DayofWeek = 7 )
SELECT @IsWeekend = 1
ELSE
SELECT @IsWeekend = 0
SELECT @relDay = DATEDIFF(DAY, CURRENT_TIMESTAMP, @FullDate)
SELECT @relWeek = -1 * DATEDIFF(ww, DATEADD(dd,-@@datefirst,@FullDate), DATEADD(dd,-@@datefirst,CURRENT_TIMESTAMP))
SELECT @relMonth = DATEDIFF(MONTH, CURRENT_TIMESTAMP, @FullDate)
SELECT @relQuarter = DATEDIFF(QUARTER, CURRENT_TIMESTAMP, @FullDate)
SELECT @relYear = DATEDIFF(YEAR, CURRENT_TIMESTAMP, @FullDate)
IF ( @DayofMonth < DATEPART(DAY, CURRENT_TIMESTAMP) )
SELECT @mtd = 1
ELSE
SELECT @mtd = 0
IF ( @mtd = 1 AND @CalYEAR = YEAR(CURRENT_TIMESTAMP) )
SELECT @mtdzv = 1
ELSE
SELECT @mtdzv = 0
IF ( @FullDate < DATEFROMPARTS(@CalYEAR, MONTH(CURRENT_TIMESTAMP), DAY(CURRENT_TIMESTAMP)) )
SELECT @ytd = 1
ELSE
SELECT @ytd = 0
/*A.Zausch: 240229: Anp. für den 29.02.
IF ( @FullDate < DATEFROMPARTS(@CalYEAR, MONTH(CURRENT_TIMESTAMP), DAY(CURRENT_TIMESTAMP)-1) )
SELECT @ytd = 1
ELSE
SELECT @ytd = 0
*/
/*
A.Zausch: Überarbeitet 04.10.2016
IF ( @FullDate >= DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AND @FullDate < DATEADD(DAY, -1, DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), CURRENT_TIMESTAMP)) )
SELECT @ytdzv = 1
ELSE
SELECT @ytdzv = 0
*/
IF ( @FullDate >= DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR),year(CURRENT_TIMESTAMP)
–, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)
)
AND @FullDate < DATEADD(DAY, -1, DATEADD(WEEK, -52 * (YEAR(CURRENT_TIMESTAMP) – @CalYEAR), CURRENT_TIMESTAMP))
)
SELECT @ytdzv = 1
ELSE
SELECT @ytdzv = 0
IF (@FullDate = EOMONTH(@FullDate))
SELECT @ldom = 1
ELSE
SELECT @ldom = 0
INSERT D_Datum(
[Datum],
[Jahr (#)],
[Quartal (#)],
[Quartal (Bezeichnung)],
[Monat (#)],
[Monat (Bezeichnung)],
[Kalenderwoche (#)],
[Tag im Monat (#)],
[Tag in der Woche (#)],
[Tag (Bezeichnung)],
[Year-to-Date | YTD(1/0)],
[Year-to-Date_ZV | YTD(1/0)],
[Month-to-Date | MTD(1/0)],
[Month-to-Date_ZV | MTD(1/0)],
[Wochenende (1/0)],
[Jahresbezug (#)],
[Quartalsbezug (#)],
[Monatsbezug (#)],
[Wochenbezug (#)],
[Tagesbezug (#)],
[Jahr – Monat (#)],
[Jahr – Quartal (#)],
[Letzter Tag des Monats (1/0)]
)VALUES(
@FullDate,
@CalYEAR,
@CalQUARTER,
@CalQUARTERLabel,
@CalMONTH,
@MonthName,
@CalWEEK,
@DayofMonth,
@DayofWeek,
@DayName,
@ytd,
@ytdzv,
@mtd,
@mtdzv,
@IsWeekend,
@relYear,
@relQuarter,
@relMonth,
@relWeek,
@relDay,
@year_month,
@year_quarter,
@ldom
)
SELECT @FullDate = DATEADD(DAY, 1, @Fulldate)
END
WHILE ( @mtd_year <= DATEDIFF(YEAR, ‘2011-01-01’, CURRENT_TIMESTAMP) )
BEGIN
UPDATE D_Datum SET [Month-to-Date_ZV | MTD(1/0)] = 1 WHERE Datum IN ( SELECT DATEADD(WEEK, -52 * @mtd_year, Datum) FROM D_Datum WHERE Datum >= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1) AND [Month-to-Date_ZV | MTD(1/0)] = 1 )
SELECT @mtd_year = @mtd_year + 1
END
GO
UPDATE D_Datum SET [Year-to-Date_ZV | YTD(1/0)] = 0 WHERE
–kein Schaltjahr
–[Monatsbezug (#)]=-12 AND [Tag im Monat (#)] =1
–Schaltjahr
[Monatsbezug (#)]=-12 AND [Tag im Monat (#)] IN (1,2)
GO
Hinterlasse einen Kommentar
An der Diskussion beteiligen?Hinterlasse uns deinen Kommentar!