ÿþCREATE TABLE [dbo].[TB_Appointments] (

	[ID] [int] IDENTITY (1, 1) NOT NULL ,

	[ItemID] [varchar] (15) NULL ,

	[EntryID] [text] NULL ,

	[Start] [datetime] NULL ,

	[End] [datetime] NULL ,

	[Subject] [varchar] (255) NULL ,

	[Body] [text] NULL ,

	[Owner] [varchar] (255) NULL ,

	[Item] [varchar] (255) NULL ,

	[Actions] [int] NULL ,

	[AllDayEvent] [int] NOT NULL ,

	[BillingInformation] [varchar] (255) NULL ,

	[BusyStatus] [int] NULL ,

	[Categories] [text] NULL ,

	[Companies] [varchar] (255) NULL ,

	[ConferenceServerAllowExternal] [int] NOT NULL ,

	[ConferenceServerPassword] [varchar] (255) NULL ,

	[ConversationIndex] [text] NULL ,

	[ConversationTopic] [varchar] (255) NULL ,

	[Importance] [int] NULL ,

	[IsOnlineMeeting] [int] NOT NULL ,

	[IsRecurring] [int] NOT NULL ,

	[Links] [int] NULL ,

	[LinkIDS] [text] NULL ,

	[Location] [varchar] (255) NULL ,

	[MeetingStatus] [varchar] (255) NULL ,

	[MessageClass] [varchar] (255) NULL ,

	[Mileage] [varchar] (255) NULL ,

	[NetMeetingAutoStart] [int] NOT NULL ,

	[NetMeetingDocPathName] [varchar] (255) NULL ,

	[NetMeetingOrganizerAlias] [varchar] (255) NULL ,

	[NetMeetingServer] [varchar] (255) NULL ,

	[NetMeetingType] [int] NULL ,

	[NetShowURL] [varchar] (255) NULL ,

	[NoAging] [int] NOT NULL ,

	[OptionalAttendees] [text] NULL ,

	[Organizer] [varchar] (255) NULL ,

	[Recipients] [int] NULL ,

	[RecipientsMailAddress] [text] NULL ,

	[RecurrenceState] [int] NULL ,

	[ReminderMinutesBeforeStart] [int] NULL ,

	[ReminderOverrideDefault] [int] NOT NULL ,

	[ReminderPlaySound] [int] NOT NULL ,

	[ReminderSet] [int] NOT NULL ,

	[ReminderSoundFile] [varchar] (255) NULL ,

	[ReplyTime] [datetime] NULL ,

	[RequiredAttendees] [text] NULL ,

	[Resources] [varchar] (255) NULL ,

	[ResponseRequested] [int] NOT NULL ,

	[ResponseStatus] [int] NULL ,

	[Sensitivity] [int] NULL ,

	[Unread] [int] NOT NULL ,

	[DayOfMonth] [int] NULL ,

	[DayOfWeekMask] [int] NULL ,

	[Duration] [int] NULL ,

	[StartTime] [datetime] NULL ,

	[EndTime] [datetime] NULL ,

	[Exceptions] [int] NULL ,

	[Instance] [int] NULL ,

	[Interval] [int] NULL ,

	[MonthOfYear] [int] NULL ,

	[NoEndDate] [int] NOT NULL ,

	[Occurrences] [int] NULL ,

	[PatternEndDate] [datetime] NULL ,

	[PatternStartDate] [datetime] NULL ,

	[RecurrenceType] [int] NULL ,

	[Regenerate] [int] NOT NULL ,

	[Deleted] [int] NOT NULL ,

	[OriginalDate] [datetime] NULL ,

	[Department] [varchar] (255) NULL ,

	[Gen_Body] [int] NULL ,

	[Gen_Categories] [int] NULL ,

	[Gen_ConversationIndex] [int] NULL ,

	[Gen_EntryID] [int] NULL ,

	[Gen_LinkIDS] [int] NULL ,

	[Gen_OptionalAttendees] [int] NULL ,

	[Gen_RecipientsMailAddress] [int] NULL ,

	[Gen_RequiredAttendees] [int] NULL ,

	[s_ColLineage] [image] NULL ,

	[s_Generation] [int] NULL ,

	[s_GUID] [uniqueidentifier] NULL ,

	[s_Lineage] [image] NULL 

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



CREATE TABLE [dbo].[TB_Users] (

	[User] [varchar] (50) NULL ,

	[Department] [varchar] (50) NULL ,

	[s_ColLineage] [image] NULL ,

	[s_Generation] [int] IDENTITY (1, 1) NOT NULL ,

	[s_GUID] [uniqueidentifier] NULL ,

	[s_Lineage] [image] NULL 

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



ALTER TABLE [dbo].[TB_Appointments] WITH NOCHECK ADD 

	CONSTRAINT [DF_TB_Appointments_AllDayEvent] DEFAULT (0) FOR [AllDayEvent],

	CONSTRAINT [DF_TB_Appointments_ConferenceServerAllowExternal] DEFAULT (0) FOR [ConferenceServerAllowExternal],

	CONSTRAINT [DF_TB_Appointments_IsOnlineMeeting] DEFAULT (0) FOR [IsOnlineMeeting],

	CONSTRAINT [DF_TB_Appointments_IsRecurring] DEFAULT (0) FOR [IsRecurring],

	CONSTRAINT [DF_TB_Appointments_NetMeetingAutoStart] DEFAULT (0) FOR [NetMeetingAutoStart],

	CONSTRAINT [DF_TB_Appointments_NoAging] DEFAULT (0) FOR [NoAging],

	CONSTRAINT [DF_TB_Appointments_ReminderOverrideDefault] DEFAULT (0) FOR [ReminderOverrideDefault],

	CONSTRAINT [DF_TB_Appointments_ReminderPlaySound] DEFAULT (0) FOR [ReminderPlaySound],

	CONSTRAINT [DF_TB_Appointments_ReminderSet] DEFAULT (0) FOR [ReminderSet],

	CONSTRAINT [DF_TB_Appointments_ResponseRequested] DEFAULT (0) FOR [ResponseRequested],

	CONSTRAINT [DF_TB_Appointments_Unread] DEFAULT (0) FOR [Unread],

	CONSTRAINT [DF_TB_Appointments_NoEndDate] DEFAULT (0) FOR [NoEndDate],

	CONSTRAINT [DF_TB_Appointments_Regenerate] DEFAULT (0) FOR [Regenerate],

	CONSTRAINT [DF_TB_Appointments_Deleted] DEFAULT (0) FOR [Deleted],

	CONSTRAINT [PK_TB_Appointments] PRIMARY KEY  NONCLUSTERED 

	(

		[ID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[TB_Users] WITH NOCHECK ADD 

	CONSTRAINT [PK_TB_Users] PRIMARY KEY  NONCLUSTERED 

	(

		[s_Generation]

	)  ON [PRIMARY] 

GO



 CREATE  INDEX [ordate] ON [dbo].[TB_Appointments]([OriginalDate], [Item]) ON [PRIMARY]

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO





CREATE PROCEDURE procSpecificException



(@strID varchar(255),@EDate datetime,@OnlyDate datetime)



AS

SELECT *

From TB_Appointments

WHERE ItemID = @strID AND Item = 'Exception'  AND 

(

OriginalDate = @EDate

or

OriginalDate = @OnlyDate

)

order by ID

















GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO





CREATE PROCEDURE procAllOwners



AS



SELECT Owner FROM TB_Appointments



GROUP BY Owner



HAVING ( Owner Is Not Null )





GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



CREATE PROCEDURE procID ( @strID varchar(255) )

AS

SELECT *

From TB_Appointments

WHERE ItemID = @strID

order by ID



GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO





CREATE PROCEDURE procItem ( @strID varchar(255) )

AS

SELECT *

From TB_Appointments

WHERE ItemID = @strID AND Item = 'Item' 



order by ID



GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO





CREATE PROCEDURE procOwnersOfSpecificDepartment (@Department varchar(255) )



AS



SELECT Owner FROM TB_Appointments



WHERE Department = @Department



GROUP BY Owner



HAVING ( Owner Is Not Null )









GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO



SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO





CREATE PROCEDURE procPatern ( @strID varchar(255) )

AS

SELECT *

From TB_Appointments

WHERE ItemID = @strID AND Item = 'Pattern' 



order by ID







GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 

GO