Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Load Calendar table 10 Years out SQL 2008 r2

Posted on 2011-02-15
2
Medium Priority
?
688 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
2 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34899707
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 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