By Bob Blackburn

Many times you have to work with Fiscal calendars. Calculating a quarter end month is easy enough; but, when you have to replicate that across 12 Fiscal Year start months, the code can get unwieldy. If you don’t maintain a full Calendar in your system, you can use a short function to determine if a month is in a specific calendar period. This is useful for reporting and controlling other job stream activities.

The function below accepts the Fiscal Start Month, Test Month and Period to validate. It will return a 1 if the month matches that period.

Using a formula based calculation instead of hard coding all possible combinations reduces the code and makes it easier to read. The Modulo function (% in TSQL) returns the remainder of the divide operation. This can be used to determine every nth month from the start month.

Function Create statement:

Create FUNCTION ValidCalendarPeriod
       (@FYStartMonth int
  ,@mm int
  ,@Period char(1))

RETURNS bit
AS 
-- Validates if month is a specific calendar period.
BEGIN
    DECLARE @ret bit = 0;

set @ret = (Select CASE
When @period = 'B'  
and (1 + @FYStartMonth + @mm) % 2 = 0
Then  1
When @period = 'Q' 
and (13 - @FYStartMonth + @mm) % 3 = 0
Then 1
When @Period = 'S'
and (13 - @FYStartMonth + @mm) % 6 = 0
Then 1
When @Period = 'A'  
and (13 - @FYStartMonth + @mm) % 12 = 0
Then 1
Else 0
end)

    return @ret

END;

Here are the results from a January start month for a baseline. Then Use February, March, May and June as the test months. The query would look like this:

Select 1 as FYStart, 2 as CurrMonth,

              dbo.ValidCalendarPeriod(1,2, 'B') as ValidBiMonthly,

              dbo.ValidCalendarPeriod(1,2, 'Q') as ValidQuarter,

              dbo.ValidCalendarPeriod(1,2, 'S') as ValidSemiAnnual

              union

Select 1 as FYStart, 3 as CurrMonth,

              dbo.ValidCalendarPeriod(1,3, 'B') as ValidBiMonthly,

              dbo.ValidCalendarPeriod(1,3, 'Q') as ValidQuarter,

              dbo.ValidCalendarPeriod(1,3, 'S') as ValidSemiAnnual

              union

Select 1 as FYStart, 5 as CurrMonth,

              dbo.ValidCalendarPeriod(1,5, 'B') as ValidBiMonthly,

              dbo.ValidCalendarPeriod(1,5, 'Q') as ValidQuarter,

              dbo.ValidCalendarPeriod(1,5, 'S') as ValidSemiAnnual

              union

Select 1 as FYStart, 6 as CurrMonth,

              dbo.ValidCalendarPeriod(1,6, 'B') as ValidBiMonthly,

              dbo.ValidCalendarPeriod(1,6, 'Q') as ValidQuarter,

              dbo.ValidCalendarPeriod(1,6, 'S') as ValidSemiAnnual

Results

Now let us look at an uncommon Fiscal Start Month. We will use August. This is from a recent requirement I worked on. So, any month can come up.

A start month of August and the results for September through January.

Now you can use this function to filter your data or control work flow without rewriting date logic every time.