Introduction

Accountants sometimes break up a year based on a series of quarters divided into two four-week periods followed by a five-week period sometimes referred to as a 4-4-5 Calendar. In Part 1, I introduced one way to determine the Year of a date based on this kind of calendar. In this article, I will demonstrate a way to determine the Period of a date. The Period is similar to a month, but I use the term Period instead because the division could span actual months and I do not want to confuse Period 1 with January as Month 1. Also, if the calendar starts in a different month than January, then Period 1 should still represent the first division of the year. I will describe the function, give some samples, and then talk briefly about the performance of a function versus a set-based operation in SQL Server.

Function

I chose to use a function in order to demonstrate the calculation process based on any starting date and any starting day. This also allows me to demonstrate the calculation process that can be adjusted to populate a table of division dates. The function takes in three parameters: @Fiscal is the starting month and day of the fiscal year with the year 1900, such as 1/1/1900 or 7/1/1900; @TestDate is the date in question; @Weekday is an integer used for the day of the week to start the accounting process. The function returns a string that represents the Year and the Period, such as 2008P04 for the 4th Period in the year 2008.

/*
 Takes in a Fiscal Year and a Date
 Returns a string representing the year and the period in which 
 the date falls based on a 4-4-5 accounting method.
*/
CREATE FUNCTION [dbo].[fn_445AccountingPeriod](
 @Fiscal datetime, 
 @TestDate datetime,
 @Weekday int-- The day of the week to start accounting: 1-Sunday, 2-Monday, etc.
) 
RETURNS varchar(30) AS
BEGIN 
DECLARE @FiscalStart datetime
DECLARE @FiscalStartAdjusted datetime
DECLARE @DaysDiff int
DECLARE @Period varchar(30)
-- Determine the Fiscal Start Date for the year of the given date.
SELECT @FiscalStart = DateAdd(year, datediff(year, IsNull(@Fiscal,'1/1/1900'), @TestDate) , IsNull(@Fiscal,'1/1/1900'))
IF (@TestDate < @FiscalStart) BEGIN SELECT @FiscalStart = DateAdd(year, -1, @FiscalStart) END
-- Adjust the Fiscal Start to the first @Weekday in the Fiscal year.
SELECT @FiscalStartAdjusted = CASE WHEN DatePart(dw, @FiscalStart) = @Weekday THEN @FiscalStart 
 WHEN DatePart(dw, @FiscalStart) < @Weekday THEN DateAdd(d, @Weekday - DatePart(dw, @FiscalStart), @FiscalStart)
 ELSE DateAdd(d, @Weekday - DatePart(dw, @FiscalStart) + 7, @FiscalStart) END
-- Find the number of days between the testdate and the start of the year
SELECT @DaysDiff = DateDiff(d, @FiscalStartAdjusted, @TestDate)
-- The different periods are divided by a number of days in the 
-- 4-4-5 strategy equivalent to 28-28-35.
-- The comparison starts with most days and works backwards.
SELECT @Period = CASE 
 WHEN @DaysDiff > 371 THEN 'Error13' -- This is an error condition. The maximum days in any year should be either 364 or 371
 WHEN @DaysDiff < 0 THEN convert(varchar, YEAR(@FiscalStartAdjusted)-1) + 'P12' -- This is when the day is in the previous year of Periods.
 WHEN @DaysDiff >= 329 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P12' -- All remaining days in the year
 WHEN @DaysDiff >= 301 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P11'
 WHEN @DaysDiff >= 273 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P10'
 WHEN @DaysDiff >= 238 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P09'
 WHEN @DaysDiff >= 210 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P08'
 WHEN @DaysDiff >= 182 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P07'
 WHEN @DaysDiff >= 147 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P06'
 WHEN @DaysDiff >= 119 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P05'
 WHEN @DaysDiff >= 91 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P04' -- Repeat the pattern
 WHEN @DaysDiff >= 56 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P03' -- Third period of 35 days
 WHEN @DaysDiff >= 28 THEN convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P02' -- Second period of 28 days
 ELSE convert(varchar, YEAR(@FiscalStartAdjusted)) + 'P01' -- First period of 28 days
 END

Return @Period
END

At the beginning of the function, determine the start date of the fiscal year for the date in question by adding the difference in the years between @Fiscal and @TestDate to @Fiscal. If @TestDate is less than @Fiscal, then the date is in the previous year. In this case subtract one year from @FiscalStart. Next, adjust @FiscalStart to the first @Weekday that occurs in the year; this is accomplished here using a CASE statement. If the weekday of the fiscal start date is the same as @Weekday, then use the fiscal start date. If the weekday of the fiscal start date is less than @Weekday, then take the difference in the number of days between the two and add that to the fiscal start date. If the weekday of the fiscal start date is greater than @Weekday, then similarly take the difference between the two, adding that to the fiscal date, but also add seven more days to establish a date in the following week. This gives a fiscal start date adjusted to the first date that @Weekday occurs in the current fiscal year, @FiscalStartAdjusted.

Once @FiscalStartAdjusted is determined, the next step uses the difference in the number of days between @FiscalStartAdjusted and @TestDate. Using this difference, you can determine the Period by comparing the difference to the break points that happen in the 4-4-5 accounting scheme. In such a scheme, there would be 28 days in the first period, 28 days in the second, and 35 in the third, so, the break points would be 28, 56, and 91.This pattern would repeat for each quarter in the year. On occasion, the last period of the year will have 42 days because the 4-4-5 scheme divides the year into segments that do not follow the actual length of a calendar year.

In the CASE statement, I first check if @DaysDiff is greater than 371. This should never happen, but I include it here just in case. Next, I check to see if @DaysDiff is less than 0. When this happens, it means the date is actually in the 12th Period of the previous year. After checking these two particular cases, begin checking the number of days against the break points for each Period starting with the latest, i.e. 329 days for Period 12. Work your way down through the break points until you reach the correct break point as compared to @DaysDiff. This gives you the string that Period designation which then is returned from the function.

Samples

To demonstrate the function, create a table with a fiscal date field and a test date field.

IF EXISTS(SELECT * from sysobjects WHERE xtype = 'U' and name = 'TestDates')
DROP TABLE TestDates
GO

CREATE TABLE TestDates (
 FiscalStartDate datetime,
 TestDate datetime
)
GO

Insert an assortment of data into the table.

INSERT INTO TestDates VALUES ('1/1/1900', '12/31/2008')
INSERT INTO TestDates VALUES ('1/1/1900', '1/1/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/2/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/4/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/5/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '5/1/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '5/3/2010')
INSERT INTO TestDates VALUES ('7/1/1900', '6/30/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/1/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/2/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/6/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '10/7/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '12/31/2009')

Next, call the function in a simple SELECT statement. I return the original FiscalStartDate and TestDate from the table so that I can see the two pieces of information that give the output string. Here, I hard code a 1 for @Weekday to start the accounting on a Sunday.

SELECT 
 CONVERT(varchar,FiscalStartDate,101) AS FiscalStartDate, 
 CONVERT(varchar,TestDate,101) AS TestDate, 
 dbo.fn_445AccountingPeriod(FiscalStartDate, TestDate, 1) AS [445Period]
FROM TestDates

This short demonstration shows that an actual month does not denote the Period of a given date in the 4-4-5 accounting scheme. The date 1/1/2009 is actually in the 12th Period in 2008 because the first Sunday in 2009 does not occur until 1/4/2009. Likewise, 5/1/2009 is in the 4th Period because the 5th Period does not actually start until 5/3/2009. When using a different start date for the Fiscal Year (7/1/2009), there are similar results except that the first period starts in July, but not until 7/5/2009, which is the first Sunday in July.

Functional Versus SET Based

This functional solution was built in a scenario where the sets numbered in the thousands of rows and were used in reports that were run four to five times a month. Additionally, the fiscal start date and starting day fluctuated regularly. A functional approach can work for smaller data sets like this, but can easily and quickly suffer from scalability problems with larger data sets in the hundreds of thousands or millions. It is beyond the scope of this article to demonstrate this with the function presented, but a fairly basic predefined table of break points would offer evidence that a Set-based operation provides much better performance and scalability, especially with larger data sets.

Conclusion

In the several 4-4-5 Calendar implementations I have seen, it seems that every company has its own unique requirements. Hopefully, I have provided a starting point and information that can be helpful if you are starting down this implementation road. To summarized briefly, take the Start Date for the Fiscal Year and adjust it to the starting date for the 4-4-5 Year. Then, count the days between the date in question and this adjusted starting date. Using this difference, determine the Period for the date based on the break points for days in the 4-4-5 Calendar.