Solved

Load Calendar table 10 Years out SQL 2008 r2

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 25
select over clause 1 40
Storage Spaces 3 39
MSSQL Speen Degradation 4 10
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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now