Just thought that I would add to the discussion...I am no DBA....but forced to work in this area....so...pardon any bad code. I'm starting to write a stored procedure that will generate a US calendar based on the parameters specified...here it is so far...I have only done a brief validation of it...

It's something to look at and if you have a better way to do it, that would be great...

use ReportingDB

truncate table dimcalendarrevenue

declare @startdate date

declare @enddate date

declare @firstdayofmonth int

declare @firstdayofweek int

declare @tablename nvarchar(100)

declare @quartername nvarchar(100)

if @startdate < '01/01/1971'

begin

set @startdate = '01/01/1971'

end

else set @startdate = '01/01/2000'

set @enddate = '12/31/2020'

set @firstdayofmonth = 19

set @firstdayofweek = 5 --week day numbers are (1 = monday, 2 = tuesday, 3 = wednesday, 4 = thursday, 5 = friday, 6 = saturday, 7 = sunday)

set datefirst @firstdayofweek

set @tablename = 'dimcalendarrevenue'

set @quartername = 'Q'

while @startdate <= @enddate

begin

insert into DimCalendarRevenue

------------------------------------------------------------

-- regular calendar --

------------------------------------------------------------

select convert(int,(right(replicate('0',4) + convert(nvarchar(4),datepart(yyyy,@startdate)),4) +

right(replicate('0',2) + convert(nvarchar(2),datepart(mm,@startdate)),2) +

right(replicate('0',2) + convert(nvarchar(2),datepart(dd,@startdate)),2))) as datekey,

@startdate as [calendardate],

year(@startdate) as [calendaryear],

datepart(dy,@startdate) as [calendardayofyear],

month(@startdate) as [calendarmonth],

day(@startdate) as [calendardayofmonth],

datepart(wk,@startdate) as [calendarweekofyear],

datename(weekday,@startdate) as [calendarweekdayname],

datepart(dw,@startdate) as [calendarweekdaynumber],

datename(mm,@startdate) as [calendarmonthname],

case

when month(@startdate) in (1,2,3)

then '1'

when month(@startdate) in (4,5,6)

then '2'

when month(@startdate) in (7,8,9)

then '3'

when month(@startdate) in (10,11,12)

then '4'

end as [calendarquarter],

@quartername + CONVERT(nvarchar(1), case

when month(@startdate) in (1,2,3)

then '1'

when month(@startdate) in (4,5,6)

then '2'

when month(@startdate) in (7,8,9)

then '3'

when month(@startdate) in (10,11,12)

then '4'

end) as [calendarquartername],

-----------------------------------------------------------

-- adjusted calendar --

-----------------------------------------------------------

case

when datepart(mm,@startdate) = 1

and @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then year(dateadd(yy,-1,@startdate))

else year(@startdate)

end

else year(@startdate)

end as [adjustedyear],

datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as [adjusteddayofyear],

case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end as [adjustedmonth],

case

when (day(@startdate) < @firstdayofmonth)

and @firstdayofmonth <> 1

then datediff(dd,dateadd(mm,-1,dateadd(dd,@firstdayofmonth-day(@startdate),@startdate)),@startdate) + 1

when (day(@startdate) > @firstdayofmonth)

and @firstdayofmonth <> 1

then day(@startdate) - @firstdayofmonth + 1

when day(@startdate) = @firstdayofmonth

then 1

else day(@startdate)

end as [adjusteddayofmonth],

case

when datepart(wk,@startdate) = 53

then 1

else datepart(wk,@startdate)

end as [adjusted52weeks],

ceiling(cast(datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as float)/7) as [adjustedweekofyear],

datename(weekday,@startdate) as [adjustedweekdayname],

datename(mm,dateadd(dd,-@firstdayofmonth+1,@startdate)) as [adjustedmonthname],

case

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (1,2,3)

then 1

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (4,5,6)

then 2

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (7,8,9)

then 3

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (10,11,12)

then 4

end as [adjustedquarter],

@quartername + CONVERT(nvarchar(1),case

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (1,2,3)

then 1

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (4,5,6)

then 2

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (7,8,9)

then 3

when (case

when @firstdayofmonth <> 1

then case

when datepart(dd,@startdate) < @firstdayofmonth

then month(dateadd(mm,-1,@startdate))

else month(@startdate)

end

else month(@startdate)

end) in (10,11,12)

then 4

end) as [adjustedquartername],

-----------------------------------------------------------

-- date facts --

-----------------------------------------------------------

case

when datename(weekday,@startdate) = 'sunday' or datename(weekday,@startdate) = 'saturday'

then 'weekend'

else 'weekday'

end as [weekdaytype],

0 as [holidayflag],

'' as [holidaytype],

'' as [holidayname]

set @startdate = dateadd(dd,1,@startdate)

end

-----------------------------------------------------------

-- Update Adjusted Weeks For Reporting --

-----------------------------------------------------------

update DimCalendarRevenue

set AdjustedWeekOfYear = case

when DateKey >= (select drc2.DateKey

from DimCalendarRevenue drc2

where drc2.AdjustedWeekOfYear = drc.AdjustedWeekOfYear

and drc2.AdjustedYear = drc.AdjustedYear

and drc2.calendarweekdaynumber = 1)

then AdjustedWeekOfYear + 1

else adjustedweekofyear

end

from DimCalendarRevenue drc

update DimCalendarRevenue

set AdjustedWeekOfYear = case

when drc.AdjustedWeekOfYear = 53

then 1

else drc.adjustedweekofyear

end

from DimCalendarRevenue drc

-----------------------------------------------------------

-- Update Holidays --

-----------------------------------------------------------

-- New Year's Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'New Year''s Day'

where CalendarMonth = 1

and CalendarDayOfMonth = 1

-- Independence Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Independence Day'

where CalendarMonth = 7

and CalendarDayOfMonth = 4

-- Veteran's Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Veteran''s Day'

where CalendarMonth = 11

and CalendarDayOfMonth = 11

-- Christmas Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Christmas Day'

where CalendarMonth = 12

and CalendarDayOfMonth = 25

-- Birthday of Martin Luther King, Jr.

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Birthday of Martin Luther King, Jr. (Martin Luther King Day)'

from DimCalendarRevenue drc

where drc.CalendarMonth = 1

and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

from (select top 3 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 1

and drc2.CalendarWeekDayName = 'Monday'

order by drc2.CalendarDayOfMonth asc) td

order by td.calendardayofmonth desc)

-- Washington's Birthday

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Washington''s Birthday (Presidents'' Day)'

from DimCalendarRevenue drc

where drc.CalendarMonth = 2

and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

from (select top 3 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 2

and drc2.CalendarWeekDayName = 'Monday'

order by drc2.CalendarDayOfMonth asc) td

order by td.calendardayofmonth desc)

-- Memorial Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Memorial Day'

from DimCalendarRevenue drc

where drc.CalendarMonth = 5

and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 5

and drc2.CalendarWeekDayName = 'Monday'

order by drc2.CalendarDayOfMonth desc)

-- Labor Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Labor Day'

from DimCalendarRevenue drc

where drc.CalendarMonth = 9

and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 9

and drc2.CalendarWeekDayName = 'Monday'

order by drc2.CalendarDayOfMonth asc)

-- Columbus Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Columbus Day'

from DimCalendarRevenue drc

where drc.CalendarMonth = 10

and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

from (select top 2 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 10

and drc2.CalendarWeekDayName = 'Monday'

order by drc2.CalendarDayOfMonth asc) td

order by td.calendardayofmonth desc)

-- Thanksgiving Day

update DimCalendarRevenue

set HolidayFlag = 1,

HolidayType = 'US Federal Holiday',

HolidayName = 'Thanksgiving Day'

from DimCalendarRevenue drc

where drc.CalendarMonth = 11

and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth

from (select top 4 drc2.calendardayofmonth

from DimCalendarRevenue drc2

where drc2.CalendarYear = drc.CalendarYear

and drc2.CalendarMonth = 11

and drc2.CalendarWeekDayName = 'Thursday'

order by drc2.CalendarDayOfMonth asc) td

order by td.calendardayofmonth desc)