Build and Populate Calendar Table

SET NOCOUNT ON;
CREATE TABLE CAL_MONTH
( MonthID INT IDENTITY(1, 1)
, MonthStart DATETIME
, NextMonth DATETIME
, MonthDescr CHAR(6)
, MonthName CHAR(3)
, YearMonth INT
, MonthNum INT
);

DECLARE
@MonthStart DATETIME
, @NextMonth DATETIME
, @MonthDescr CHAR(6)
, @MonthName CHAR(3)
, @MonthNum INT
, @Year INT
, @YearMonth INT;

SET @MonthStart = ‘1/1/2000’;
SET @NextMonth = DATEADD(month, 1, @MonthStart);

WHILE @MonthStart < ‘1/1/2025’
BEGIN
SET @MonthNum = DATEPART(month, @MonthStart);
SET @MonthName = CASE @MonthNum
WHEN 1 THEN ‘JAN’
WHEN 2 THEN ‘FEB’
WHEN 3 THEN ‘MAR’
WHEN 4 THEN ‘APR’
WHEN 5 THEN ‘MAY’
WHEN 6 THEN ‘JUN’
WHEN 7 THEN ‘JUL’
WHEN 8 THEN ‘AUG’
WHEN 9 THEN ‘SEP’
WHEN 10 THEN ‘OCT’
WHEN 11 THEN ‘NOV’
ELSE ‘DEC’ END;
SET @Year = DATEPART(year, @MonthStart);
SET @YearMonth = (@Year * 100) + @MonthNum;
SET @MonthDescr = @MonthName + ‘-‘ + RIGHT(CONVERT(VARCHAR, @Year), 2);

INSERT INTO CAL_MONTH
( MonthStart, NextMonth, MonthDescr, MonthName, YearMonth, MonthNum )
SELECT
@MonthStart, @NextMonth, @MonthDescr, @MonthName, @YearMonth, @MonthNum;

SET @MonthStart = @NextMonth;
SET @NextMonth = DATEADD(month, 1, @NextMonth);
END
GO

ALTER TABLE [dbo].[CAL_MONTH] ADD CONSTRAINT [PK_CAL_MONTH] PRIMARY KEY CLUSTERED
(
MonthID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF
, FILLFACTOR = 100) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [CAL_MONTH_Dates] ON [dbo].[CAL_MONTH]
(
[MonthStart] ASC,
[NextMonth] ASC
)
INCLUDE ( [MonthDescr])
WITH (PAD_INDEX = OFF, FILLFACTOR = 100)
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s