Solved

Load Calendar table 10 Years out SQL 2008 r2

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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