SasDev
asked on
Load Calendar table 10 Years out SQL 2008 r2
I have been asked to load this calendar table 10 years out. There is a holiday flag field that will indicate 1 for the days of New Year's, Memorial, Independence, Labor, Thanksgiving, and Christmas. I also have to consider Leap years.
I've searched a bit for some examples and I'm still left needing some direction as to how to get started, or possibly a link to some solid examples.
Traci M.
CREATE TABLE [dbo].[DimCalendar](
[DateWK] [int] NOT NULL,
[DateNK] [smalldatetime] NOT NULL,
[FullDate] [varchar](20) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [tinyint] NOT NULL,
[DayName] [varchar](9) NOT NULL,
[DayAbbrev] [char](3) NOT NULL,
[Weekday] [bit] NOT NULL,
[Holiday] [bit] NOT NULL,
[MonthNum] [tinyint] NOT NULL,
[MonthName] [varchar](10) NOT NULL,
[MonthAbbrev] [char](3) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[YearNum] [smallint] NOT NULL,
[FiscalWeek] [tinyint] NOT NULL,
[FiscalMonth] [tinyint] NOT NULL,
[FiscalYear] [smallint] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[DateWK] 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
I've searched a bit for some examples and I'm still left needing some direction as to how to get started, or possibly a link to some solid examples.
Traci M.
CREATE TABLE [dbo].[DimCalendar](
[DateWK] [int] NOT NULL,
[DateNK] [smalldatetime] NOT NULL,
[FullDate] [varchar](20) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [tinyint] NOT NULL,
[DayName] [varchar](9) NOT NULL,
[DayAbbrev] [char](3) NOT NULL,
[Weekday] [bit] NOT NULL,
[Holiday] [bit] NOT NULL,
[MonthNum] [tinyint] NOT NULL,
[MonthName] [varchar](10) NOT NULL,
[MonthAbbrev] [char](3) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[YearNum] [smallint] NOT NULL,
[FiscalWeek] [tinyint] NOT NULL,
[FiscalMonth] [tinyint] NOT NULL,
[FiscalYear] [smallint] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[DateWK] 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.sqlservercentral.com/scripts/time+dimension/70488/