Post date: Apr 8, 2010 8:13:54 AM
CREATE TABLE [dbo].[MailReceiverGroup]( [MailReceiverGroupID] [int] IDENTITY(1,1) NOT NULL, [MailReceiverGroupName] [varchar](100) NULL, [Summate] [bit] NULL DEFAULT ((0)),PRIMARY KEY CLUSTERED ( [MailReceiverGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF-- ------------------------------------------------------------------------------------------- ---- ------------------------------------------------------------------------------------------- ---- ------------------------------------------------------------------------------------------- --CREATE TABLE [dbo].[ReportSchedule]( [MailReceiverGroupID] [int] NOT NULL, [ReportDate] [int] NOT NULL, [Periodicity] [int] NOT NULL, [FirstMonth] [int] NOT NULL, CONSTRAINT [PK_ReportSchedule] PRIMARY KEY CLUSTERED ( [MailReceiverGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[ReportSchedule] WITH CHECK ADD CONSTRAINT [FK_ReportSchedule_MailReceiverGroup] FOREIGN KEY([MailReceiverGroupID])REFERENCES [dbo].[MailReceiverGroup] ([MailReceiverGroupID])GOALTER TABLE [dbo].[ReportSchedule] CHECK CONSTRAINT [FK_ReportSchedule_MailReceiverGroup]-- ------------------------------------------------------------------------------------------- ---- ------------------------------------------------------------------------------------------- ---- ------------------------------------------------------------------------------------------- --GOCREATE VIEW [dbo].[v_ReportSchedule]ASSELECT ROW_NUMBER() OVER(ORDER BY MailReceiverGroupID ASC) AS 'ScheduleID' , MailReceiverGroupId , CAST( CAST( YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST( MonthNumber AS VARCHAR(2)) + '-' + CAST( ReportDate AS VARCHAR(2)) AS DATETIME) AS ScheduledDate FROM ( SELECT 1 AS MonthNumber UNION SELECT 2 AS MonthNumber UNION SELECT 3 AS MonthNumber UNION SELECT 4 AS MonthNumber UNION SELECT 5 AS MonthNumber UNION SELECT 6 AS MonthNumber UNION SELECT 7 AS MonthNumber UNION SELECT 8 AS MonthNumber UNION SELECT 9 AS MonthNumber UNION SELECT 10 AS MonthNumber UNION SELECT 11 AS MonthNumber UNION SELECT 12 AS MonthNumber ) DATE CROSS JOIN ReportSchedule WHERE ((MonthNumber - FirstMonth) % Periodicity) = 0