UFN_CALCFISCALPERIOD

create function [BBDW].[UFN_CALCFISCALPERIOD]
               (@FiscalYearStart varchar(5),
                @FiscalYearEnd   varchar(5)  = '',
                @CurrentDate     datetime,
                @PeriodRequired  int  = 0)
returns datetime
as
  begin
    -- Arguments @PeriodRequired = 0 Current Fiscal Year Start, 1 Current Fiscal Year End
    -- Arguments @PeriodRequired = 2 Next Fiscal Year Start, 3 Next Fiscal Year End
    -- Arguments @PeriodRequired = 4 Prev Fiscal Year Start, 5 Prev Fiscal Year End
    declare  @FiscalMonthStart int
    declare  @CurrentYear int
    declare  @CurrentMonth int
    declare  @CurrentFiscalYear int
    declare  @FiscalPeriod datetime
    declare  @YearEndDate datetime
    declare  @YearEnd varchar(5)
    declare  @StartDate datetime
    declare  @EndDate datetime
    set @CurrentYear = year(@CurrentDate)
    set @CurrentMonth = month(@CurrentDate)
    set @FiscalMonthStart = (select case 
                                      when len(@FiscalYearStart) = 1 then convert(int,left(@FiscalYearStart,1))
                                      else convert(int,left(@FiscalYearStart,2))
                                    end)
    set @CurrentFiscalYear = (select case 
                                       when @FiscalMonthStart = 1 then @CurrentYear
                                                                         + 1
                                       when @FiscalMonthStart > 1
                                            and @CurrentMonth >= @FiscalMonthStart then @CurrentYear
                                                                                          + 1
                                       when @CurrentMonth < @FiscalMonthStart then @CurrentYear
                                     end)
    set @YearEndDate = (select case 
                                 when @FiscalMonthStart = 1 then convert(datetime,@FiscalYearStart
                                                                                    + '/'
                                                                                    + convert(varchar(4),@CurrentFiscalYear
                                                                                                           - 1))
                                                                   - 1
                                 else convert(datetime,@FiscalYearStart
                                                         + '/'
                                                         + convert(varchar(4),@CurrentFiscalYear))
                                        - 1
                               end)
    if @FiscalYearEnd = ''
      set @YearEnd = convert(varchar(5),@YearEndDate,101)
    else
      set @YearEnd = @FiscalYearEnd
    set @StartDate = convert(datetime,@FiscalYearStart
                                        + '/'
                                        + convert(varchar(4),@CurrentFiscalYear
                                                               - 1))
    set @EndDate = convert(datetime,@YearEnd
                                      + '/'
                                      + convert(varchar(4),@CurrentFiscalYear))
    if @FiscalMonthStart = 1 -- Adjustment to Year of Month starts in Jan 
      set @EndDate = dateadd(YY,-1,@EndDate)
    if @PeriodRequired = 0    -- Current Fiscal Start Date 
      set @FiscalPeriod = @StartDate
    if @PeriodRequired = 1    -- Current Fiscal Start Date 
      set @FiscalPeriod = @EndDate
    if @PeriodRequired = 2  -- Next Fiscal Start Date 
      set @FiscalPeriod = dateadd(YY,1,@StartDate)
    if @PeriodRequired = 3  -- Next Fiscal End Date 
      set @FiscalPeriod = dateadd(YY,1,@EndDate)
    if @PeriodRequired = 4  -- Previous Fiscal Start Date 
      set @FiscalPeriod = dateadd(YY,-1,@StartDate)
    if @PeriodRequired = 5  -- Previous Fiscal End Date 
      set @FiscalPeriod = dateadd(YY,-1,@EndDate)
    return (@FiscalPeriod)
  end;