/************/
/* CALENDAR */
/************/

DROP TABLE 
	IF EXISTS [DIM].[Calendar]
GO

CREATE TABLE [DIM].[Calendar](
	[calendar_key] [int] NULL,
	[calendar_date] [date] NULL,
	[calendar_date_time] [datetime] NULL,
	[calendar_year] [nvarchar](32) NULL,
	[calendar_quarter] [int] NULL,
	[calendar_quarter_abbrev] [char](2) NULL,
	[calendar_quarter_text] [varchar](32) NULL,
	[calendar_month] [int] NULL,
	[calendar_month_name] [nvarchar](32) NULL,
	[calendar_month_abbrev] [nvarchar](32) NULL,
	[day_of_year] [smallint] NULL,
	[month_day] [smallint] NULL,
	[week] [smallint] NULL,
	[week_day] [smallint] NULL,
	[week_end_flag] [char](1) NULL,
	[week_day_name] [nvarchar](16) NULL,
	[week_day_abbrev] [nvarchar](5) NULL
) ON [DIM]
GO

TRUNCATE TABLE [DIM].[Calendar];

DECLARE @SeedDate DATETIME; 
DECLARE @EndDate DATETIME; 

SET @SeedDate = '01-01-2000';
SET @EndDate = '01-01-2020';

WHILE (@SeedDate <= @EndDate)
BEGIN

INSERT INTO [DIM].[Calendar]
SELECT
	CONVERT(INT,
		(
		CONVERT(VARCHAR(4),DATEPART(yy,@SeedDate)) +
		(
		CASE 
		WHEN DATEPART(mm,@SeedDate) <= 9 THEN 
			'0' + CONVERT(VARCHAR(1),DATEPART(mm,@SeedDate))
		ELSE CONVERT(VARCHAR(2),DATEPART(mm,@SeedDate))
		END
		) +
		(
		CASE 
		WHEN DATEPART(dd,@SeedDate) <= 9 THEN 
			'0' + CONVERT(VARCHAR(1),DATEPART(dd,@SeedDate))
		ELSE CONVERT(VARCHAR(2),DATEPART(dd,@SeedDate))
		END
		) 
		--CONVERT(VARCHAR(2),DATEPART(dd,@SeedDate))
		) 
	) AS [calendar_key],


	CONVERT(DATE,@SeedDate) AS [calendar_date],
    @SeedDate AS [calendar_date_time],
	DATEPART(yy,@SeedDate) AS  [calendar_year],
	DATEPART(qq,@SeedDate) AS  [calendar_quarter],

	CASE
		WHEN DATEPART(qq,@SeedDate) = 1 THEN 'Q1'
		WHEN DATEPART(qq,@SeedDate) = 2 THEN 'Q2'
		WHEN DATEPART(qq,@SeedDate) = 3 THEN 'Q3'
		WHEN DATEPART(qq,@SeedDate) = 4 THEN 'Q4'
	END AS [calendar_quarter_abbrev],

	CASE
		WHEN DATEPART(qq,@SeedDate) = 1 THEN 'First Quarter'
		WHEN DATEPART(qq,@SeedDate) = 2 THEN 'Second Quarter'
		WHEN DATEPART(qq,@SeedDate) = 3 THEN 'Third Quarter'
		WHEN DATEPART(qq,@SeedDate) = 4 THEN 'Fourth Quarter'
	END AS [calendar_quarter_text],

	DATEPART(mm,@SeedDate) AS  [calendar_month],

	CASE
		WHEN DATEPART(mm,@SeedDate) = 1 THEN 'January'
		WHEN DATEPART(mm,@SeedDate) = 2 THEN 'February'
		WHEN DATEPART(mm,@SeedDate) = 3 THEN 'March'
		WHEN DATEPART(mm,@SeedDate) = 4 THEN 'April'
		WHEN DATEPART(mm,@SeedDate) = 5 THEN 'May'
		WHEN DATEPART(mm,@SeedDate) = 6 THEN 'June'
		WHEN DATEPART(mm,@SeedDate) = 7 THEN 'July'
		WHEN DATEPART(mm,@SeedDate) = 8 THEN 'August'
		WHEN DATEPART(mm,@SeedDate) = 9 THEN 'September'
		WHEN DATEPART(mm,@SeedDate) = 10 THEN 'October'
		WHEN DATEPART(mm,@SeedDate) = 11 THEN 'November'
		WHEN DATEPART(mm,@SeedDate) = 12 THEN 'December'
	END AS [calendar_month_name],

	CASE
		WHEN DATEPART(mm,@SeedDate) = 1 THEN 'Jan'
		WHEN DATEPART(mm,@SeedDate) = 2 THEN 'Feb'
		WHEN DATEPART(mm,@SeedDate) = 3 THEN 'Mar'
		WHEN DATEPART(mm,@SeedDate) = 4 THEN 'Apr'
		WHEN DATEPART(mm,@SeedDate) = 5 THEN 'May'
		WHEN DATEPART(mm,@SeedDate) = 6 THEN 'Jun'
		WHEN DATEPART(mm,@SeedDate) = 7 THEN 'Jul'
		WHEN DATEPART(mm,@SeedDate) = 8 THEN 'Aug'
		WHEN DATEPART(mm,@SeedDate) = 9 THEN 'Sept'
		WHEN DATEPART(mm,@SeedDate) = 10 THEN 'Oct'
		WHEN DATEPART(mm,@SeedDate) = 11 THEN 'Nov'
		WHEN DATEPART(mm,@SeedDate) = 12 THEN 'Dec'
	END AS [calendar_month_abbrev],

	DATEPART(dy,@SeedDate) AS [day_of_year],

	DATEPART(dd,@SeedDate) AS [month_day],

	DATEPART(ww,@SeedDate) AS [week],
	DATEPART(dw,@SeedDate) AS [week_day],

	CASE
		WHEN (
			DATEPART(dw,@SeedDate) = 1 
			OR DATEPART(dw,@SeedDate) = 7
			) THEN 'T'
		ELSE 'F'
	END AS [week_end_flag],

	CASE
		WHEN DATEPART(dw,@SeedDate) = 1 THEN 'Sunday'
		WHEN DATEPART(dw,@SeedDate) = 2 THEN 'Monday'
		WHEN DATEPART(dw,@SeedDate) = 3 THEN 'Tuesday'
		WHEN DATEPART(dw,@SeedDate) = 4 THEN 'Wednesday'
		WHEN DATEPART(dw,@SeedDate) = 5 THEN 'Thursday'
		WHEN DATEPART(dw,@SeedDate) = 6 THEN 'Friday'
		WHEN DATEPART(dw,@SeedDate) = 7 THEN 'Saturday'
	END AS [week_day_name],

		CASE
		WHEN DATEPART(dw,@SeedDate) = 1 THEN 'Sun'
		WHEN DATEPART(dw,@SeedDate) = 2 THEN 'Mon'
		WHEN DATEPART(dw,@SeedDate) = 3 THEN 'Tues'
		WHEN DATEPART(dw,@SeedDate) = 4 THEN 'Weds'
		WHEN DATEPART(dw,@SeedDate) = 5 THEN 'Thurs'
		WHEN DATEPART(dw,@SeedDate) = 6 THEN 'Fri'
		WHEN DATEPART(dw,@SeedDate) = 7 THEN 'Sat'
	END AS [week_day_abbrev];

	SET @SeedDate = DATEADD(dd,1,@SeedDate)

	END
GO