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 🙂