Solved

Load Calendar table 10 Years out SQL 2008 r2

Posted on 2011-02-15
2
681 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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