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;

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