if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RetrieveAllEvents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RetrieveAllEvents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RetrieveEventsByDay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RetrieveEventsByDay]
GO

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

CREATE TABLE [dbo].[tblEvents] (
	[pkEventID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EventStartDate] [datetime] NOT NULL ,
	[EventStartTime] [datetime] NOT NULL ,
	[EventEndDate] [datetime] NOT NULL ,
	[EventEndTime] [datetime] NOT NULL ,
	[IsPublic] [bit] NOT NULL ,
	[RecurrenceType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RecurrenceStart] [datetime] NULL ,
	[RecurrenceEnd] [datetime] NULL ,
	[PeriodicCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PeriodicAmount] [tinyint] NULL ,
	[MonthlyCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MonthlyOrdinal] [tinyint] NULL ,
	[MonthlyDay] [tinyint] NULL ,
	[AnniversaryDate] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEvents] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblEvents] PRIMARY KEY  CLUSTERED 
	(
		[pkEventID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblEvents] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblEvents_IsPublic] DEFAULT (0) FOR [IsPublic],
	CONSTRAINT [DF_tblEvents_RecurrenceType] DEFAULT ('N') FOR [RecurrenceType]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_RetrieveAllEvents
AS
SELECT *, IsRecurrence = (CASE RecurrenceType WHEN 'N' THEN 'No'  ELSE 'Yes' END)
FROM tblEvents
ORDER BY EventStartDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure sp_RetrieveEventsByDay
@DateInput datetime
as
select * from tblEvents
where (@DateInput between eventstartdate and eventenddate)
or (@DateInput between recurrencestart and recurrenceend)
or (@DateInput >= recurrencestart and recurrenceend Is Null)
Order By EventStartTime
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

