What is a Fiscal Year? Also known as a financial or tax year, it is an accounting time period of 12-months that may or may not coincide with the calendar year. A fiscal year is the time period used to group various financial statements together for a given company or organization to create an annual or yearly report. Some companies have fiscal years that match the calendar year, but this is not the norm. Most companies and organizations have non-calendar year fiscal years that begin and end on the same date every year. The US Government has a fiscal year that runs from October 1st to September 30th. Some companies have fiscal years that last 52 weeks so that the fiscal year starts on the same day of the week every year - in affect 2 days less than an actual year.
Cisco is an example of a company that has a 52 week fiscal year. Another characteristic of fiscal years is that they sometime change when companies undergo mergers, buyouts, or restructuring.
This blog post represents a possible solution for implementing Fiscal Years in your database. For most companies and organizations it would likely be ok to hardcode fiscal year information/calculations within user-defined functions or within other code blocks, however this solution is more generic, data driven and flexible.
The foundation for the solution is a simple table named FiscalYear (
note: code tested on Microsoft SQL Server 2008 Dev Ed):
CREATE TABLE FiscalYear
(FiscalYear int NOT NULL,
BeginDate date NOT NULL,
EndDate date NOT NULL,
CONSTRAINT PK_FiscalYear_FiscalYear PRIMARY KEY CLUSTERED (FiscalYear ASC))
Add the following CHECK CONSTRAINTS to the FiscalYear table to ensure quality of data. The first requires BeginDate to be before EndDate. The second requires that FiscalYear value match the Year(EndDate) value - this is the norm, the year of the end date defines the fiscal year.
ALTER TABLE dbo.FiscalYear ADD CONSTRAINT CK_FiscalYear_BeginDate_lt_EndDate CHECK (BeginDate < EndDate);
ALTER TABLE dbo.FiscalYear ADD CONSTRAINT CK_FiscalYear_FiscalYear_equals_EndDateYear CHECK (FiscalYear = YEAR(EndDate));
Next populate the new FiscalYear table with some sample data:
INSERT FiscalYear (FiscalYear, BeginDate, EndDate) VALUES (2007, '20061001', '20070930');
INSERT FiscalYear (FiscalYear, BeginDate, EndDate) VALUES (2008, '20071001', '20080930');
INSERT FiscalYear (FiscalYear, BeginDate, EndDate) VALUES (2009, '20081001', '20090930');
INSERT FiscalYear (FiscalYear, BeginDate, EndDate) VALUES (2010, '20091001', '20100930');
INSERT FiscalYear (FiscalYear, BeginDate, EndDate) VALUES (2011, '20101001', '20110930');
Please note that valid data for this table requires that there be no overlaps or gaps in the FiscalYear values and BeginDate/EndDate values. Later in this post I will provide an solution to the intra-table data validation problem.
Next we look at querying the data contained within the table. We want to encapsulate the following code within a user-defined function so that it behaves very similarly to the Transact-SQL
YEAR scalar function:
DECLARE @InputDate date;
SET @InputDate = '20091201';
SELECT fy.FiscalYear FROM FiscalYear fy WHERE @InputDate BETWEEN fy.BeginDate AND fy.EndDate;
Looking at the Execution Plan of the above SELECT query a Clustered Index Scan is used - an Index Seek is preferred. Add the following index to speed performance.
CREATE NONCLUSTERED INDEX IX_FiscalYear_BeginDate_EndDate_FiscalYear ON FiscalYear
(BeginDate ASC, EndDate ASC, FiscalYear ASC)
The SELECT query above should now used an Index Seek (Non-Clustered) which should improve performance. Now convert the SELECT query into a user-defined scalar-valued function.
CREATE FUNCTION dbo.udfFiscalYear(@InputDate date = NULL)
RETURNS int
AS
BEGIN
DECLARE @Return int;
IF (@InputDate IS NULL)
BEGIN
SET @InputDate = GETDATE();
END
SELECT @Return = fy.FiscalYear FROM dbo.FiscalYear fy WHERE @InputDate BETWEEN fy.BeginDate AND fy.EndDate;
RETURN @Return;
END
If a NULL value is passed into the dbo.udfFiscalYear function, it is assumed that you are requesting the current fiscal year. In this case, the @InputDate value is converted to GETDATE() before the FiscalYear table lookup is done. Also, if no matching row in the FiscalYear table is found, the function returns NULL.
We now have the basic structures (lookup table and user-defined function) in place to implement fiscal years within your database. In subsequent post I will describe specific uses of this fiscal year implementation.
Finally, in this post I will supply a solution to the problem of ensuring there are no gaps or overlaps in the data contained within the FiscalYear table. This solution is not ideal because it is not a real-time solution but rather after-the-fact. Potentially bad data could be entered into the table but not caught and hopefully fixed until after the following variance query below is ran. The query relies on two user-defined functions that are listed.
CREATE FUNCTION dbo.udfGetPreviousFiscalYearEndDate(@InputFiscalYear int)
RETURNS date
AS
BEGIN
DECLARE @Return date;
SELECT @Return = fy.EndDate FROM dbo.FiscalYear fy WHERE fy.FiscalYear = (@InputFiscalYear - 1);
RETURN @Return;
END
and
CREATE FUNCTION dbo.udfGetNextFiscalYearBeginDate(@InputFiscalYear int)
RETURNS date
AS
BEGIN
DECLARE @Return date;
SELECT @Return = fy.BeginDate FROM dbo.FiscalYear fy WHERE fy.FiscalYear = (@InputFiscalYear + 1);
RETURN @Return;
END
The following query should show 1s (ones) across the board for PreviousEndDateDiff and NextBeginDateDiff (except for first fiscal year which will show NULL for PreviousEndDateDiff and last fiscal year which will show NULL for NextBeginDateDiff).
SELECT fy.FiscalYear, fy.BeginDate, fy.EndDate,
DATEDIFF(d, dbo.udfGetPreviousFiscalYearEndDate(fy.FiscalYear), fy.BeginDate) PreviousEndDateDiff,
DATEDIFF(d, fy.EndDate, dbo.udfGetNextFiscalYearBeginDate(fy.FiscalYear)) NextBeginDateDiff
FROM FiscalYear fy
ORDER BY fy.FiscalYear
If 1s (ones) are not shown then you have problems with the FiscalYear table data that needs to be fixed.
In closing, in a future post I will explore better solutions to the data validation problem possibly using CHECK CONSTRAINTS, triggers, stored procedures or other mechanisms. I strongly prefer a real-time data validation mechanism over an after-the-fact find and cleanup process. Also, as stated before I will write further about using this fiscal year implementation in a practical way in your database.
If you have any ideas, criticisms, or questions please let me know. Any feedback is appreciated! Thanks!