-- Create_Table.Reference.Months.sql

USE [Dates]
GO

/*
DROP TABLE IF EXISTS [Reference].[Months]
GO
*/

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Reference].[Months]') AND type in (N'U'))
BEGIN
CREATE TABLE [Reference].[Months](
	-- Month
	[MonthKey]				int IDENTITY(1,1) NOT NULL,
	[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,

	-- Quarter
	[QuarterNumber]			int NOT NULL,
	[QuarterEnglish]		varchar(32) NOT NULL,
	[QuarterFrench]			varchar(32) NOT NULL,
	[FiscalQuarterNumber]	int NOT NULL,
	[FiscalQuarterEnglish]	varchar(32) NOT NULL,
	[FiscalQuarterFrench]	varchar(32) NOT NULL,

	-- Created/Modified
	[CreatedBy]				varchar(50) NOT NULL,
	[CreatedOn]				datetime NOT NULL,
	[ModifiedBy]			varchar(50) NULL,
	[ModifiedOn]			datetime NULL,
 CONSTRAINT [PK_Reference_Months] PRIMARY KEY CLUSTERED 
(
	[MonthKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO


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

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