if exists (select * from dbo.sysobjects where id = object_id(N'dim_holiday') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dim_holiday
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dim_holiday_3NF') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dim_holiday_3NF
GO

CREATE TABLE dim_holiday 
(
  holiday_key int NOT NULL
, year_num datetime NULL
, new_years_day datetime NULL
, mlk_day datetime NULL
, ground_hog_day datetime NULL
, lincolns_birthday datetime NULL
, valentines_day datetime NULL
, washingtons_birthday datetime NULL
, presidents_day datetime NULL
, st_patricks_day datetime NULL
, april_fools_day datetime NULL
, good_friday datetime NULL
, easter datetime NULL
, mothers_day datetime NULL
, memorial_day datetime NULL
, fathers_day datetime NULL
, us_independence_day datetime NULL
, parents_day datetime NULL
, labor_day datetime NULL
, grandparents_day datetime NULL
, columbus_day datetime NULL
, halloween datetime NULL
, veterans_day datetime NULL
, thanksgiving_day datetime NULL
, christmas_day datetime NULL
, effective_date datetime NULL
, expiration_date datetime NULL
, is_current AS (case when (expiration_date = convert(datetime,'2599-12-31')) then 'Y' else 'N' end) 
)
GO

CREATE TABLE dbo.dim_holiday_3NF 
(
  holiday_key int NOT NULL
, holiday_name varchar (40) NULL
, year_num datetime NULL
, holiday_date datetime NULL
, effective_date datetime NULL
, expiration_date datetime NULL
, is_current AS (case when (expiration_date = convert(datetime,'2599-12-31')) then 'Y' else 'N' end) 
)
GO

ALTER TABLE dbo.dim_holiday 
WITH NOCHECK 
ADD CONSTRAINT PK_dim_holiday 
PRIMARY KEY CLUSTERED (	holiday_key )
GO

ALTER TABLE dbo.dim_holiday_3NF 
WITH NOCHECK 
ADD CONSTRAINT PK_dim_holiday_3NF 
PRIMARY KEY CLUSTERED (	holiday_key )
GO

ALTER TABLE dbo.dim_holiday 
ADD CONSTRAINT DF_dim_holiday_effective_date DEFAULT (getdate()) FOR effective_date
  , CONSTRAINT DF_dim_holiday_expiration_date DEFAULT (convert(datetime,'2599-12-31')) FOR expiration_date
GO


ALTER TABLE dbo.dim_holiday_3NF 
ADD CONSTRAINT DF_dim_holiday_3NF_effective_date DEFAULT (getdate()) FOR effective_date
  , CONSTRAINT DF_dim_holiday_3NF_expiration_date DEFAULT (convert(datetime,'2599-12-31')) FOR expiration_date
GO

exec sp_addextendedproperty N'MS_Description', N'Denormalized US Holiday Table', N'user', N'dbo', N'table', N'dim_holiday'
GO

exec sp_addextendedproperty N'MS_Description', N'3-N-F US Holiday Table', N'user', N'dbo', N'table', N'dim_holiday_3NF'
GO


exec sp_addextendedproperty N'MS_Description', N'Date of April Fools Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'april_fools_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Christmas Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'christmas_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Columbus Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'columbus_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Christian Easter ', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'easter'
GO
exec sp_addextendedproperty N'MS_Description', N'Date the annual holiday record becomes effective; defaults to current database date and time', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'effective_date'
GO
exec sp_addextendedproperty N'MS_Description', N'Date the annual holiday row expires or is replaced; defaults to 2599-12-31', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'expiration_date'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Fathers Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'fathers_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Good Friday (Friday before Easter)', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'good_friday'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Grandparents Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'grandparents_day'
GO
exec sp_addextendedproperty N'MS_Description', N'date of Ground Hog Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'ground_hog_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Halloween', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'halloween'
GO
exec sp_addextendedproperty N'MS_Description', N'surrogate key; integer; uniquely identifies each occurence of a year''s holidays', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'holiday_key'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines if the current row is the most current version of annual holidays for this year; should be computed based on expiration_date = ''2599-12-31''', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'is_current'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Labor Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'labor_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of US President Lincolns Birthday', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'lincolns_birthday'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of US Memorial Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'memorial_day'
GO
exec sp_addextendedproperty N'MS_Description', N'date of Martin Luther King Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'mlk_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Mothers Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'mothers_day'
GO
exec sp_addextendedproperty N'MS_Description', N'date of new years day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'new_years_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Parents Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'parents_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Presidents Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'presidents_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of St. Patricks Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'st_patricks_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of US Thanksgiving Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'thanksgiving_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of US Independence Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'us_independence_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of Valentines Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'valentines_day'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of US Veterans Day', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'veterans_day'
GO
exec sp_addextendedproperty N'MS_Description', N'US President Washingtons Birthday', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'washingtons_birthday'
GO
exec sp_addextendedproperty N'MS_Description', N'the year number for which these holidays are valid', N'user', N'dbo', N'table', N'dim_holiday', N'column', N'year_num'
GO


exec sp_addextendedproperty N'MS_Description', N'date this version of this holiday for this year becomes effective; defaults to current database date and time', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'effective_date'
GO
exec sp_addextendedproperty N'MS_Description', N'date this holiday for this year expires or is replaced; defaults to ''2599-12-31'';', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'expiration_date'
GO
exec sp_addextendedproperty N'MS_Description', N'Date of the holiday', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'holiday_date'
GO
exec sp_addextendedproperty N'MS_Description', N'surrogate key; integer; uniquely identifies this version of the annual holidays from all others;', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'holiday_key'
GO
exec sp_addextendedproperty N'MS_Description', N'provides a displayable holiday name or description; ', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'holiday_name'
GO
exec sp_addextendedproperty N'MS_Description', N'Year number with which this holiday is associated', N'user', N'dbo', N'table', N'dim_holiday_3NF', N'column', N'year_num'
GO

/*
Don McMunn is a professional, business-focused, consultant specializing in data warehousing and business intelligence design, development and delivery for over a decade with the Fortune 500 and SMB segments.  He may be reached at:  dmcmunn at ipcdesigns dot com
*/