Group: General Forum Members
Points: 76
Visits: 48
|
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)
|