Monday, March 15, 2010

Financial Quarter Calculator

I saw a question on dbforums.com asking for how to determine the financial quarter value for a given date.  I think the solution below will give one a good start on that:

if object_id('dbo.fn__MonthWithOffset') is not null
   drop function dbo.fn__MonthWithOffset
go
create function dbo.fn__MonthWithOffset (
   @monthNumber tinyint
  ,@monthOffset tinyint
  ,@operation   char(1) /* + or - */    ) returns tinyint
as begin
      declare @result tinyint
      if @operation not in ('+','-')
         return (null)
      if @monthNumber not between 1 and 12
         return (null)
      if @operation = '+' begin
         if (cast(@monthNumber as int) + cast(@monthOffset as int)) > 12
            set @result = cast((cast(@monthNumber as int) + cast(@monthOffset as int)) % 12 as tinyint)
         else
            set @result = @monthNumber + @monthOffset
      end else if @operation = '-' begin
         if @monthOffset > 12
            set @monthOffset = cast((@monthOffset % 12) as tinyint)
         if (cast(@monthNumber as int) - @monthOffset) <= 0
            set @result = 12 - (@monthOffset - @monthNumber)
         else
            set @result = @monthNumber - @monthOffset
      end
      return (@result)
   end
go

if object_id('dbo.fn__FinancialQuarter') is not null
   drop function dbo.fn__FinancialQuarter
go
create function dbo.fn__FinancialQuarter (
   @date datetime
  ,@firstMonth tinyint                   ) returns tinyint
as begin
      return (
         select
            case datepart(month, @date)
               when dbo.fn__MonthWithOffset(@firstMonth, 0, '+')  then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 01, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 02, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 03, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 04, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 05, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 06, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 07, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 08, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 09, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 10, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 11, '+') then 4
            end
      )
   end
go

select dbo.fn__FinancialQuarter ('11/01/2009', 11)

No comments:

Post a Comment