-- Create_Table.Reference.Days.sql

USE [Dates]
GO

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

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Reference].[Days]') AND type in (N'U'))
BEGIN
CREATE TABLE [Reference].[Days](
	-- Day
	[DayKey]				int IDENTITY(1,1) NOT NULL,
	[DayNumber]				int NOT NULL,
	[DayEnglish]			varchar(32) NOT NULL,
	[DayFrench]				varchar(32) NOT NULL,
	[DayShortEnglish]		varchar(3) NOT NULL,
	[DayShortFrench]		varchar(3) NOT NULL,
	[DayInitialEnglish]		varchar(1) NOT NULL,
	[DayInitialFrench]		varchar(1) NOT NULL,
	[DayOfWeekNumber]		int NOT NULL,

	-- Flags
	[IsWeekday]				int NOT NULL,
	[IsBusinessDay]			int NOT NULL,

	-- Created/Modified
	[CreatedBy]				varchar(50) NOT NULL,
	[CreatedOn]				datetime NOT NULL,
	[ModifiedBy]			varchar(50) NULL,
	[ModifiedOn]			datetime NULL,
 CONSTRAINT [PK_Reference_Days] PRIMARY KEY CLUSTERED 
(
	[DayKey] 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_Days_CreatedBy]') AND type = 'D')
BEGIN
ALTER TABLE [Reference].[Days] ADD  CONSTRAINT [DF_Reference_Days_CreatedBy]  DEFAULT (user_name()) FOR [CreatedBy]
END
GO

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