Solved

Load Calendar table 10 Years out SQL 2008 r2

Posted on 2011-02-15
2
683 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:SasDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34899707
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34900132
you can use tgis to get started...

it will generate 10years plus 8 days worth of dates...

just add in the other datename/part requirements...

after the main insert i'd then update the required dates for holiday flags ....



insert into calendar

select datepart(wk,fulldate),fulldate,fulldate
        ,datepart(dw,fulldate)
       ,....

   from (select dateadd(d,d.number+(y.number*366),startdate) as FullDate
              from (select '20110101' as startdate) as x
              cross join (select number from master.dbo.spt_values where  type='p' and number between 0 and 366) as d
              cross join (select number from master.dbo.spt_values where  type='p' and number between 0 and 9) as y

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question