-- Create_Table.dbo.ReferenceDates.sql

USE [Dates]
GO

DROP TABLE IF EXISTS [Reference].[Dates]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Reference].[Dates]') AND type in (N'U'))
BEGIN
CREATE TABLE [Reference].[Dates](
	-- General
	[DateKey]					int NOT NULL,
	[DateText]					varchar(8) NOT NULL,
	[DateDate]					date NOT NULL,
	[DateDatetime]				datetime NOT NULL,

	-- Year
	[YearNumber]				int NOT NULL,

	-- Quarter
	[QuarterNumber]				int NOT NULL,
	[QuarterEnglish]			varchar(2) NOT NULL,
	[QuarterFrench]				varchar(2) NOT NULL,
	[YearQuarterNumber]			int NOT NULL,

	-- Month
	[MonthNumber]				int NOT NULL,
	[MonthEnglish]				varchar(32) NOT NULL,
	[MonthFrench]				varchar(32) NOT NULL,
	[MonthShortEnglish]			varchar(4) NOT NULL,
	[MonthShortFrench]			varchar(4) NOT NULL,
	[MonthInitialEnglish]		varchar(1) NOT NULL,
	[MonthInitialFrench]		varchar(1) NOT NULL,
	[YearMonthNumber]			int NOT NULL,
	
	-- Day
	[DayOfMonthNumber]			int NOT NULL,
	[DayOfWeekNumber]			int NOT NULL,
	[DayEnglish]				varchar(15) NOT NULL,
	[DayFrench]					varchar(15) NOT NULL,
	[DayShortEnglish]			varchar(3) NOT NULL,
	[DayShortFrench]			varchar(3) NOT NULL,
	[DayInitialEnglish]			varchar(1) NOT NULL,
	[DayInitialFrench]			varchar(1) NOT NULL,

	-- Fiscal Year
	[FiscalYearNumber]			int NOT NULL,
	[FiscalYear]				varchar(9) NOT NULL,

	-- Fiscal Quarter
	[FiscalQuarterNumber]		int NOT NULL,
	[FiscalQuarterEnglish]		varchar(3) NOT NULL,
	[FiscalQuarterFrench]		varchar(3) NOT NULL,
	[FiscalYearQuarterNumber]	int NULL,
	
	-- Holiday
	[HolidayNameEnglish]		varchar(50) NULL,
	[HolidayNameFrench]			varchar(50) NULL,

	-- Offset
	[YearOffset]			int NULL,
	[FiscalYearOffset]		int NULL,
	[QuarterOffset]			int NULL,
	[MonthOffset]			int NULL,
	[DayOffset]				int NULL,

	-- Flags (fixed)
	[IsWeekday]				bit NOT NULL,
	[IsWeekendDay]			bit NOT NULL,
	[IsBusinessDay]			bit NOT NULL,
	[IsHoliday]				bit NULL,

	-- Created/Modified
	[CreatedBy]				varchar(50) NOT NULL,
	[CreatedOn]				datetime NOT NULL,
	[ModifiedBy]			varchar(50) NULL,
	[ModifiedOn]			datetime NULL	
)
END
GO

SET ANSI_PADDING OFF
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Reference_Dates_CreatedBy]') AND type = 'D')
BEGIN
ALTER TABLE [Reference].[Dates] ADD  CONSTRAINT [DF_Reference_Dates_CreatedBy]  DEFAULT (user_name()) FOR [CreatedBy]
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Reference_Dates_CreatedOn]') AND type = 'D')
BEGIN
ALTER TABLE [Reference].[Dates] ADD  CONSTRAINT [DF_Reference_Dates_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
END

GO


