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

(

Period char(6),

CalDate Datetime,

PeriodYear SmallInt,

PeriodMonth SmallInt

)

Create Index IX_FiscalCalendar_Date on FiscalCalendar (CalDate)

Create Index IX_FiscalCalendar_Period on FiscalCalendar (Period)

Create Table FiscalPeriods

(

PeriodMonth smallint,

PeriodStartDate Datetime,

PeriodEndDate Datetime,

Period char(6)

)

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

(

@myNum int,

@myLength int

)

Returns char(10)

As

Begin

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))

return ltrim(rtrim(@Retvalue))

end

And here's the procedure itself. It takes the year and the day of the first month end as parameters.

Create Procedure CreateFiscalCalendarYear

(

@Year int,

@Day int

)

As

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

(

CalDate, PeriodYear

)

Select top (@NumDays)

DateAdd(Day, N, @YearStartDate), year(DateAdd(Day, N, @YearStartDate))

FromTally1000 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

(

Mydate datetime,

pMonth int

)

insert into @myTable

Select CalDate, dense_rank()

over(partition by year(CalDate) order by CalDate)

from fiscalcalendar where datepart(weekday, CalDate) = 6 and

datepart(week,CalDate) in

(

@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.

Update FiscalCalendar

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

(

PeriodEndDate

)

Select CalDate

From fiscalcalendar

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.

UpdateFiscalPeriods

setPeriodStartDate = cast(year(PeriodEndDate) as char) + '-01-01'

wheremonth(PeriodEndDate) = 1 and Year(PeriodEndDate) = @Year

UpdateFiscalPeriods

setPeriodEndDate = cast(year(PeriodEndDate) as char) + '-12-31'

wheremonth(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

Update FiscalPeriods

setPeriodStartDate =

(

select dateadd(day, 1, max(PeriodEndDate))

fromFiscalPeriods 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

(

Period Char(4),

PeriodStartDate datetime,

PeriodEndDate datetime

)

Insert into @MyTable2

select dense_rank()

over(partition by year(PeriodStartDate)

order by PeriodEndDate), PeriodStartDate, PeriodEndDate

From FiscalPeriods

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)

from @MyTable2

-- 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

UpdateFiscalCalendar

setPeriodMonth = a.PeriodMonth

From FiscalPeriods a

wherePeriodYear = @Year and caldate between a.PeriodStartDate and

a.PeriodEndDate

-- Period Codes

UpdateFiscalCalendar

setPeriod = cast(PeriodYear as char(4)) + dbo.zerofill(PeriodMonth,2)

wherePeriodYear = @Year

-- And create the procedure

go

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

(

@vDate datetime

)

returns datetime

as

begin

declare @wDate datetime

Set @wDate = CAST(FLOOR(CAST(@vDate AS FLOAT ))AS DATETIME)

Return @wDate

End

Getting the start date of the fiscal period for a given date.

Create Function GetFiscalBOM(@Date Datetime) returns Datetime

as

Begin

Declare @RetValue datetime

Select @RetValue = PeriodStartDate

from FiscalPeriods

where @Date between PeriodStartDate and PeriodEndDate

Return @RetValue

end

Getting the Period Code (YYYYMM) for a given date

Create Function GetFiscalPeriod(@Date datetime) returns Char(6)

as

Begin

Declare @RetValue char(6)

select @RetValue = period

from FiscalCalendar

where CalDate = dbo.getshortdate(@Date)

Return @RetValue

End

Getting the start date of a given period

Create Function GetPeriodStartdate(@Period char(6))

returns datetime

as

Begin

Declare @RetValue Datetime

Select @RetValue = PeriodStartDate From FiscalPeriods

where Period = @Period

Return @RetValue

End

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 🙂