Just Code‎ > ‎

T SQL - Just some handsome code to configure a shedule

posted Apr 8, 2010, 1:13 AM by Peter Henell
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]

GO
SET 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]

GO
ALTER TABLE [dbo].[ReportSchedule]  WITH CHECK ADD  CONSTRAINT [FK_ReportSchedule_MailReceiverGroup] FOREIGN KEY([MailReceiverGroupID])
REFERENCES [dbo].[MailReceiverGroup] ([MailReceiverGroupID])
GO
ALTER TABLE [dbo].[ReportSchedule] CHECK CONSTRAINT [FK_ReportSchedule_MailReceiverGroup]

-- ------------------------------------------------------------------------------------------- --
-- ------------------------------------------------------------------------------------------- --
-- ------------------------------------------------------------------------------------------- --

GO
CREATE VIEW [dbo].[v_ReportSchedule]
AS


SELECT 
    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
Comments