Untitled
unknown
plain_text
3 years ago
8.0 kB
4
Indexable
EXEC('
CREATE OR ALTER VIEW[dwh].[ALG_DimDatum] as
WITH DateRange as (
SELECT CurrentDate = DATEADD(DAY, rn - 1, DATEFROMPARTS(YEAR(GETDATE())-10,1,1))
FROM
(
SELECT TOP (DATEDIFF(DAY, DATEFROMPARTS(YEAR(GETDATE())-10,1,1),
DATEFROMPARTS(YEAR(GETDATE())+2,1,1))) rn = ROW_NUMBER() OVER (ORDER BY a.object_id)
FROM sys.all_objects a CROSS JOIN sys.all_objects b
) AS dates
where DATEADD(DAY, rn - 1, DATEFROMPARTS(YEAR(GETDATE())-10,1,1))>=''2018-01-01''
),
DateQueryENG as
(SELECT
DateBK = CONVERT (char(8),CurrentDate,112)
,DateAsInteger = CONVERT(int,CONVERT (char(8),CurrentDate,112))
,Date = CurrentDate
,DayOfMonth = DATEPART(DD, CurrentDate)
,DayName = DATENAME(DW, CurrentDate)
,DayOfWeek = DATEPART(DW, CurrentDate)
,DayOfYear = DATEPART(DY, CurrentDate)
,WeekOfMonth = DATEPART(WW, CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, CurrentDate)) + ''/1/'' + CONVERT(VARCHAR, DATEPART(YY, CurrentDate)))
,WeekOfQuarter = (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, CurrentDate), 0), CurrentDate) / 7) + 1
,WeekOfYear = DATEPART(ISO_WEEK, CurrentDate)
,Month = DATEPART(MM, CurrentDate)
,MonthName = DATENAME(MM, CurrentDate)
,MonthNumName = RIGHT(''00'' + CONVERT(VARCHAR(2),MONTH(CurrentDate)),2) + '' - '' + CONVERT(VARCHAR, DATENAME(MM, CurrentDate))
,MonthOfQuarter = CASE
WHEN DATEPART(MM, CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, CurrentDate) IN (3, 6, 9, 12) THEN 3
END
,Quarter = DATEPART(QQ, CurrentDate)
,QuarterName = CONCAT(''Q'',DATEPART(QQ, CurrentDate))
,Year = DATEPART(YEAR, CurrentDate)
,MonthYear = LEFT(DATENAME(MM, CurrentDate), 3) + ''-'' + CONVERT(VARCHAR, DATEPART(YY, CurrentDate))
,MMYYYY = RIGHT(''0'' + CONVERT(VARCHAR, DATEPART(MM, CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, CurrentDate))
,YYYYMM = CONVERT(char(6),CurrentDate,112)
,FirstDayOfMonth = CONVERT(date, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, CurrentDate) - 1), CurrentDate)))
,LastDayOfMonth = CONVERT(date, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, CurrentDate)))), DATEADD(MM, 1, CurrentDate))))
,FirstDayOfQuarter = CONVERT(date, DATEADD(QQ, DATEDIFF(QQ, 0, CurrentDate), 0))
,LastDayOfQuarter = CONVERT(date, DATEADD(QQ, DATEDIFF(QQ, -1, CurrentDate), -1))
,FirstDayOfYear = CONVERT(date, ''01/01/'' + CONVERT(VARCHAR, DATEPART(YY, CurrentDate)))
,LastDayOfYear = CONVERT(date, ''12/31/'' + CONVERT(VARCHAR, DATEPART(YY, CurrentDate)))
,IsWeekDay = CASE DATEPART(DW, CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END
,[IsCurrentYear] = CASE WHEN YEAR(CurrentDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentQuarter] = CASE WHEN CASE WHEN YEAR(CurrentDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END = 1 AND DATEPART(QUARTER, CurrentDate) = DATEPART(QUARTER, GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentMonth] = CASE WHEN CASE WHEN YEAR(CurrentDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END = 1 AND DATEPART(MONTH, CurrentDate) = DATEPART(MONTH, GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentWeek] = CASE WHEN CASE WHEN YEAR(CurrentDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END = 1 AND DATEPART(WEEK, CurrentDate) = DATEPART(WEEK, GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentDay] = CASE WHEN CurrentDate = CONVERT (char(8), GETDATE(),112) THEN 1 ELSE 0 END
,[IsPreviousYear] = CASE WHEN YEAR(CurrentDate) = YEAR(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) THEN 1 ELSE 0 END
,[IsCurrentQuarterLY] = CASE WHEN YEAR(CurrentDate) = YEAR(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) AND DATEPART(QUARTER, CurrentDate) = DATEPART(QUARTER, GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentMonthLY] = CASE WHEN YEAR(CurrentDate) = YEAR(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) AND DATEPART(MONTH, CurrentDate) = DATEPART(MONTH, GETDATE()) THEN 1 ELSE 0 END
,[IsCurrentWeekLY] = CASE WHEN YEAR(CurrentDate) = YEAR(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) AND DATEPART(WEEK, CurrentDate) = DATEPART(WEEK, GETDATE()) THEN 1 ELSE 0 END
,[IsPreviousQuarter] = CASE WHEN CurrentDate BETWEEN DATEADD(QQ, DATEDIFF(qq, -1, GETDATE()) - 2, 0) AND DATEADD(ss, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) THEN 1 ELSE 0 END
,[IsPreviousMonth] = CASE WHEN CurrentDate BETWEEN DATEADD(month, DATEDIFF(month, -1, GETDATE()) - 2, 0) AND DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) THEN 1 ELSE 0 END
,[IsPreviousWeek] = CASE WHEN CurrentDate BETWEEN DATEADD(WEEK, DATEDIFF(WEEK, -1, GETDATE()) - 1, 0) AND DATEADD(ss, -1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)) THEN 1 ELSE 0 END
FROM
DateRange dr )
select
DatumBK = DateBK
,DatumInteger = DateAsInteger
,Datum = Date
,DagVanMaand = DayOfMonth
,DagNaam = CASE DayName
WHEN ''Monday'' THEN ''Maandag''
WHEN ''Tuesday'' THEN ''Dinsdag''
WHEN ''Wednesday'' THEN ''Woensdag''
WHEN ''Thursday'' THEN ''Donderdag''
WHEN ''Friday'' THEN ''Vrijdag''
WHEN ''Saturday'' THEN ''Zaterdag''
WHEN ''Sunday'' THEN ''Zondag''
Else NULL end
,DagVanWeek = DayOfWeek
,DagVanJaar = DayOfYear
,WeekVanMaand = WeekOfMonth
,WeekVanKwartaal = WeekOfQuarter
,WeekVanJaar = WeekOfYear
,Maand = Month
,MaandNaam = CASE Month
WHEN 1 THEN ''januari''
WHEN 2 THEN ''februari''
WHEN 3 THEN ''maart''
WHEN 4 THEN ''april''
WHEN 5 THEN ''mei''
WHEN 6 THEN ''juni''
WHEN 7 THEN ''juli''
WHEN 8 THEN ''augustus''
WHEN 9 THEN ''september''
WHEN 10 THEN ''oktober''
WHEN 11 THEN ''november''
WHEN 12 THEN ''december''
ELSE NULL END
,MaandNumName = CONCAT(LEFT(MonthNumName,5),
CASE Month
WHEN 1 THEN ''januari''
WHEN 2 THEN ''februari''
WHEN 3 THEN ''maart''
WHEN 4 THEN ''april''
WHEN 5 THEN ''mei''
WHEN 6 THEN ''juni''
WHEN 7 THEN ''juli''
WHEN 8 THEN ''augustus''
WHEN 9 THEN ''september''
WHEN 10 THEN ''oktober''
WHEN 11 THEN ''november''
WHEN 12 THEN ''december''
ELSE NULL END)
,MaandOfKwartaal = MonthOfQuarter
,Kwartaal = Quarter
,KwartaalName = QuarterName
,Jaar = Year
,MaandJaar = MonthYear
,MMYYYY = MMYYYY
,YYYYMM = YYYYMM
,EersteDagOfMaand = FirstDayOfMonth
,LaatsteDagOfMaand = LastDayOfMonth
,EersteDagOfKwartaal = FirstDayOfQuarter
,LaatsteDagOfKwartaal = LastDayOfQuarter
,EersteDagOfJaar = FirstDayOfYear
,LaatsteDagOfJaar = LastDayOfYear
,IsWeekDag = IsWeekDay
,[IsHuidigJaar] = [IsCurrentYear]
,[IsHuidigKwartaal] = [IsCurrentQuarter]
,[IsHuidigMaand] = [IsCurrentMonth]
,[IsHuidigWeek] = [IsCurrentWeek]
,[IsHuidigDag] = [IsCurrentDay]
,[IsVorigJaar] = [IsPreviousYear]
,[IsHuidigKwartaalVJ] = [IsCurrentQuarterLY]
,[IsHuidigMaandVJ] = [IsCurrentMonthLY]
,[IsHuidigWeekVJ] = [IsCurrentWeekLY]
,[IsVorigeKwartaal] = [IsPreviousQuarter]
,[IsVorigeMaand] = [IsPreviousMonth]
,[IsVorigeWeek] = [IsPreviousWeek]
from DateQueryENG')Editor is loading...