I know, old topic but better late than never.
I had to build calendar tables a while ago. Recently I was force to revisit this because of a little trick the accounting department played on us. We use a 4-4-5 calendar but with certain specifics.
1: Fiscal years always start on January 1st and end on December 31.
2: Our periods end on Fridays
3: For 2010 they've decided to push the first period up to January 29 instead of the 22nd because they didn't want the last period to be 6 weeks long.
Because of this I had to rewrite the code that generates my calendar tables. Here's how I do it. I use 2 tables, FiscalCalendar to keep the periods for every day of the year ans FiscalPeriods to keep.......fiscal periods :-). To speed up any code that will use them I also created a few indexes.
Create Table FiscalCalendar
Create Index IX_FiscalCalendar_Date on FiscalCalendar (CalDate)
Create Index IX_FiscalCalendar_Period on FiscalCalendar (Period)
Create Table FiscalPeriods
Create Index IX_FiscalPeriods_Period on FiscalPeriods (Period)
Create Index IX_FIscalPeriods_Month on FiscalPeriods (PeriodMonth)
Create Index IX_FiscalPeriods_StartDate on FiscalPeriods (PeriodStartDate)
In the code that populates the tables I use a function that I wrote a long time ago. I'm sure it could be rewritten to be much nicer but it's simple and it served me well for a long time so there's no point in rewriting it .........if it's not broken............
ZeroFill receives and integer and returns a string of the requested length padded with leading zeroes. It is very useful to convert year 2010 period 1 into 201001.
Create Function ZeroFill
declare @StrValue char(20)
declare @RetValue char(20)
set @StrValue = cast(@myNum as char(20))
set @RetValue = replicate('0',@MyLength-len(@strValue)) + ltrim(rtrim(@StrValue))
And here's the procedure itself. It takes the year and the day of the first month end as parameters.
Create Procedure CreateFiscalCalendarYear
Declare @YearStartdate datetime
Declare @YearEndDate Datetime
Declare @FirstClosingDate Datetime
Declare @NumDays int
Declare @Weeks01 int
Declare @Weeks02 int
Declare @Weeks03 int
Declare @Weeks04 int
Declare @Weeks05 int
Declare @Weeks06 int
Declare @Weeks07 int
Declare @Weeks08 int
Declare @Weeks09 int
Declare @Weeks10 int
Declare @Weeks11 int
Declare @Weeks12 int
-- The 12 @Weeks variables keep the week numbers of each month end.
Set @FirstClosingDate = cast(@Year as char(04)) + '-01-' + dbo.zerofill(@Day,2)
Set @YearStartDate = cast(@Year -1 as char(4)) + '-12-31'
Set @YearEndDate = cast(@Year as char(4)) + '-12-31'
-- Note that I set my @YearStartDate variable to the last day of the previous year.
-- That's because it's used to calculate the number of days of my year using DateX - DateY
-- and I want it include January 1st. Not very nice but efficient and that calculation is
-- necessary because we need to know if we're on a 365 days year or 366.
-- Get the week of the first month end. This will be either 1 or 2.
-- Then apply the remaining of a 4-4-5 "mask" to get the other week numbers.
Set @Weeks01 = DatePart(Week, @FirstClosingDate)
Set @Weeks02 = @Weeks01 + 4
Set @Weeks03 = @Weeks02 + 5
Set @Weeks04 = @Weeks03 + 4
Set @Weeks05 = @Weeks04 + 4
Set @Weeks06 = @Weeks05 + 5
Set @Weeks07 = @Weeks06 + 4
Set @Weeks08 = @Weeks07 + 4
Set @Weeks09 = @Weeks08 + 5
Set @Weeks10 = @Weeks09 + 4
Set @Weeks11 = @Weeks10 + 4
Set @Weeks12 = @Weeks11 + 5
-- Get the number of days in the year. We will use it to create the
-- right number of records in the FiscalCalendar table.
Set @NumDays = (select DateDiff(day, @YearStartDate,@YearEndDate ))
-- We all make mistakes and sometimes we have to re-run the code. Since our tables
-- may already contain the information for the year we're working with we need to clean
-- it up
Delete from FiscalCalendar where year(CalDate) = @Year
Delete from FiscalPeriods where Year(PeriodStartDate) = @Year
-- and then create our data starting with the FiscalCalendar table.
-- Using a Tally table (Thanks Jeff ) we can easily create our @Numdays records.
Insert Into FiscalCalendar
Select top (@NumDays)
DateAdd(Day, N, @YearStartDate), year(DateAdd(Day, N, @YearStartDate))
From Tally1000 order by N
-- Next I update the period month for the days that mark the end of a period.
-- Using dense_rank and my @Weeks variable I can create a memory table
-- that will hold my 12 end dates.
-- Note that "where datepart(weekday,caldate) = 6 specifies my periods
-- end on Fridays, which is day 6 of the week.
Declare @myTable Table
insert into @myTable
Select CalDate, dense_rank()
over(partition by year(CalDate) order by CalDate)
from fiscalcalendar where datepart(weekday, CalDate) = 6 and
@Weeks01, @Weeks02, @Weeks03, @Weeks04, @Weeks05, @Weeks06,
@Weeks07, @Weeks08, @Weeks09, @Weeks10, @Weeks11, @Weeks12
-- And then write them back to the FiscalCalendar table. The other columns will be
-- populated later.
set PeriodMonth = a.pMonth
from @myTable a
where myDate = fiscalcalendar.CalDate
-- Time to get started with the FiscalPeriods table.
-- We start by creating our 12 records. This is pretty easy since
-- we happen to have 12 records in the FiscalCalendar table that
-- have their period end dates.
insert into FiscalPeriods
where PeriodMonth is not null and year(caldate) = @Year
order by Caldate
-- Then we need to update the PeriodStartDate and PeriodEndDate columns.
-- Two of them are easy. We know that the start date of the first period is January 1
-- and that the end date of the last period is December 31.
set PeriodStartDate = cast(year(PeriodEndDate) as char) + '-01-01'
where month(PeriodEndDate) = 1 and Year(PeriodEndDate) = @Year
set PeriodEndDate = cast(year(PeriodEndDate) as char) + '-12-31'
where month(PeriodEndDate) = 12 and Year(PeriodEndDate) = @Year
-- With those values in the table we can use a sub-query to update each PeriodStartDate
-- with the previous PeriodEndDate + 1
set PeriodStartDate =
select dateadd(day, 1, max(PeriodEndDate))
from FiscalPeriods a
where a.PeriodEndDate < fiscalPeriods.PeriodEndDate
where PeriodStartDate is null and Year(PeriodEndDate) = @Year
-- The FiscalPeriods table require one last update to populate
-- the Period column. Once again I'm using a memory table
-- and dense_rank
declare @MyTable2 Table
Insert into @MyTable2
over(partition by year(PeriodStartDate)
order by PeriodEndDate), PeriodStartDate, PeriodEndDate
where Year(PeriodStartDate) = @Year
-- With my memory table loaded I only need to delete records for that year
-- in my physical table and reload it.
Delete from FiscalPeriods where Year(PeriodStartDate) = @Year
Insert into FiscalPeriods
PeriodMonth, PeriodStartDate, PeriodEndDate, Period
Select Period, PeriodStartDate, PeriodEndDate,
cast(year(periodstartdate) as char(4))+ dbo.zerofill(period,2)
-- With my FiscalPeriods table complete, I can come back to the FiscalCalendar
-- table and update the empty columns that I didn't handle before from
-- the FiscalPeriods table
-- Period Months
set PeriodMonth = a.PeriodMonth
From FiscalPeriods a
where PeriodYear = @Year and caldate between a.PeriodStartDate and
-- Period Codes
set Period = cast(PeriodYear as char(4)) + dbo.zerofill(PeriodMonth,2)
where PeriodYear = @Year
-- And create the procedure
Now I can add a year to my fiscal calendar with a simple procedure call passing it the year and the day of the first month end.
Exec CreateFiscalCalendarYear 2010, 29
My FiscalCalendar table contains an auto-increment integer column defined as Primary Key which I didn't put here. It's used to build relations with other tables to create fiscal calendar dimensions in my cubes.
I also have a number of Fiscal Calendar oriented functions that are used in other processes.
GetShortDate(): Very useful when trying to link a datetime field that contains the time part with the calendar.
Create Function GetShortDate
declare @wDate datetime
Set @wDate = CAST(FLOOR(CAST(@vDate AS FLOAT ))AS DATETIME)
Getting the start date of the fiscal period for a given date.
Create Function GetFiscalBOM(@Date Datetime) returns Datetime
Declare @RetValue datetime
Select @RetValue = PeriodStartDate
where @Date between PeriodStartDate and PeriodEndDate
Getting the Period Code (YYYYMM) for a given date
Create Function GetFiscalPeriod(@Date datetime) returns Char(6)
Declare @RetValue char(6)
select @RetValue = period
where CalDate = dbo.getshortdate(@Date)
Getting the start date of a given period
Create Function GetPeriodStartdate(@Period char(6))
Declare @RetValue Datetime
Select @RetValue = PeriodStartDate From FiscalPeriods
where Period = @Period
I'm sure some will criticize my code and come up with much nicer ways of doing this but so far I haven't seen any code on the forum to build a 4-4-5 calendar starting on January 1st, ending on December 31 and with a first month end date different than where 4 weeks puts you.
Hopefully it can help some people. At least it will show Jeff one more coder who actually did it......I even use the Tally table technique that I learned from your article