CREATE PROCEDURE [dbo].[pBuildDim_Date]
(
@StartYear int = 1998
,@NumYears int = 15
)
AS
DECLARE
@TmpDate smalldatetime
,@EndDate smalldatetime
,@LastModDate smalldatetime
SET DATEFIRST 1 -- Week starts on Monday
SET @TmpDate = CONVERT(smalldatetime, ('1/1/' + CONVERT(varchar(4), @StartYear)))
SET @EndDate = DATEADD(yyyy, @NumYears, @TmpDate - 1)
SET @LastModDate = GETDATE()
-- Remove all members.
TRUNCATE TABLE dbo.DimDate
-- Add member to represent unknown or default.
INSERT INTO dbo.DimDate
(
DateKey
,[Date]
,YYYYMMDD
,[DateName]
,[Year]
,[Q]
,[Qtr]
,[Quarter]
,[QuarterName]
,[MM]
,[Mon]
,[Month]
,[MonthName]
,[DD]
,[Dy]
,[Day]
,[DayOfWeek]
,[Julian]
,[WeekKey]
,[Week]
,[WeekName]
,[Weekend]
,YearKey
,MoKey
,MMMYY
,MMMDD
,DayActive
,HolidayFlag
,DTM
)
VALUES
(
19000101 -- DateKey
,'1900-01-01' -- Date
,'19000101' -- YYYYMMDD
,'01/01/1900' -- DateName
,1900 -- Year
,0 -- Q
,'Q0-00' -- Qtr
,'Quarter 0' -- Quarter
,'Q0CY00' -- QuarterName
,0 -- MM
,'NA' -- Mon
,'NA' -- Month
,'NONE' -- MonthName
,0 -- DD
,'NA' -- Dy
,'NONE' -- Day
,0 -- DayOfWeek
,0 -- Julian
,0 -- WeekKey
,0 -- Week
,'NONE' -- WeekName
,0 -- WeekEnd
,-1 -- YearKey
,190001 -- MoKey
,'NONE' -- MMMYY
,'NONE' -- MMMDD
,0 -- DayActive
,0 -- HolidayFlag (not specified)
,@LastModDate -- DTM
)
-- Add regular date members.
WHILE @TmpDate <= @EndDate
BEGIN
INSERT INTO dbo.DimDate
(
DateKey
,[Date]
,YYYYMMDD
,[DateName]
,[Year]
,[Q]
,[Qtr]
,[Quarter]
,[QuarterName]
,[MM]
,[Mon]
,[Month]
,[MonthName]
,[DD]
,[Dy]
,[Day]
,[DayOfWeek]
,[Julian]
,[WeekKey]
,[Week]
,[WeekName]
,[Weekend]
,YearKey
,MoKey
,MMMYY
,MMMDD
,DayActive
,HolidayFlag
,DTM
)
VALUES
(
CONVERT(int, CONVERT(nchar(8), @TmpDate, 112)) -- DateKey
,@TmpDate -- Date
,CONVERT(nchar(8), @TmpDate, 112) -- YYYYMMDD
,CONVERT(nvarchar(10), @TmpDate, 101) -- DATENAME
,DATEPART(yyyy, @TmpDate) -- Year
,DATEPART(qq, @TmpDate) -- Q
,'Q' + CAST(DATEPART(q, @TmpDate) AS nchar(1)) + '-' + RIGHT(CAST(DATEPART (yy, @TmpDate) AS nchar(4)), 2) -- Qtr
,'Quarter ' + CAST(DATEPART(q, @TmpDate) as nchar(1)) -- Quarter
,'Q' + CAST(DATEPART(q, @TmpDate) AS nchar(1)) + 'CY' + RIGHT(CAST(DATEPART(yy, @TmpDate) AS nvarchar(4)), 2) -- QuarterName
,DATEPART(mm, @TmpDate) -- MM
,LEFT(DATENAME(mm, @TmpDate), 3) -- Mon
,DATENAME(mm, @TmpDate) -- Month
,DATENAME(mm, @TmpDate) + ' ' + CAST(DATEPART(yy, @TmpDate) AS nvarchar(4)) -- MonthName
,DATEPART(dd, @TmpDate) -- DD
,LEFT(DATENAME(dw, @TmpDate), 3) -- Dy
,DATENAME(dw, @TmpDate) -- Day
,DATEPART(dw, @TmpDate) -- DayOfWeek
,DATEPART(dy, @TmpDate) -- Julian
,CONVERT(int, CAST(YEAR(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(4))
+ CASE
WHEN LEN(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101)))) = 1 THEN '0' + CAST(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(1))
ELSE CAST(MONTH(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(2))
END
+ CASE
WHEN LEN(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101)))) = 1 THEN '0' + CAST(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(1))
ELSE CAST(DAY(DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))) AS nchar(2))
END) -- WeekKey
,DATEPART(wk, @TmpDate) -- Week
,CAST((CONVERT(nchar(10), (DATEADD(d, (DATEPART(dw, @TmpDate) - 1) * (-1), CONVERT(nvarchar(10), @TmpDate, 101))), 101) + ' - '
+CONVERT(nchar(10), (DATEADD(d, (7 - DATEPART(dw, @TmpDate) ), CONVERT(nvarchar(10), @TmpDate, 101))), 101)) AS nvarchar(25)) -- WeekName
,CASE
WHEN DATEPART(dw, @TmpDate) in (6, 7) THEN 1
ELSE 0
END -- Weekend
,@NumYears - (DATEPART(yyyy, @TmpDate) - @StartYear) -- YearKey
,DATEPART(yyyy, @TmpDate) * 100 + DATEPART(mm, @TmpDate) -- MoKey
,LEFT(DATENAME(mm, @TmpDate),3) + '-' + RIGHT(CAST(DATEPART(yy, @TmpDate) AS nchar(4)), 2) -- MMMYY
,LEFT(DATENAME(mm, @TmpDate),3) + '-' + CAST(DATEPART(dd, @TmpDate) AS nchar(2)) -- MMMDD
,CASE
WHEN DATEDIFF(m, @TmpDate, @EndDate) > 25 THEN 0
ELSE 1
END -- DayActive
,0 -- HolidayFlag (not specified)
,@LastModDate -- DTM
)
SET @TmpDate = DATEADD(dd, 1, @TmpDate)
END
SET QUOTED_IDENTIFIER OFF