USP_ADHOCQUERY_FISCALYEARROLLINGDATEHELPER

create procedure dbo.[USP_ADHOCQUERY_FISCALYEARROLLINGDATEHELPER] (
  @ASOFDATE date,
  @DELTA integer
)
with execute as owner
as begin
  set nocount on;

  declare @DATES table ([STARTDATE] date, [ENDDATE] date, [ERRORCODE] integer);
  declare @SQL nvarchar(max);
  declare @SEQUENCE integer;
  declare @GLFISCALYEARID uniqueidentifier;
  declare @STARTDATE date;
  declare @ENDDATE date;

  -- if anything goes wrong at any point while calculating fiscal year start and end dates - if the interval takes you outside of the range of configured

  -- fiscal years, for example -- return null for both dates and let the caller decide what to do


  insert into @DATES values (null, null, 0);

  if object_id(N'dbo.GLFISCALPERIOD', N'U') is not null and object_id(N'dbo.GLFISCALYEAR', N'U') is not null
    begin
      -- 1. get the fiscal year within which the as-of date falls


      set @SQL = N'select @SEQUENCE = [YEARSEQUENCE] ' +
                  'from dbo.[GLFISCALYEAR] ' + 
                  'inner join dbo.[GLFISCALPERIOD] on [GLFISCALPERIOD].[GLFISCALYEARID] = [GLFISCALYEAR].[ID] ' +
                  'where @ASOFDATE between [GLFISCALPERIOD].[STARTDATE] and [GLFISCALPERIOD].[ENDDATE];';

      exec sp_executesql @SQL, N'@SEQUENCE integer output, @ASOFDATE date', @SEQUENCE = @SEQUENCE output, @ASOFDATE = @ASOFDATE;

      if @SEQUENCE is not null
        begin
          -- 2. use the YEARSEQUENCE field to move forward or back N fiscal years

          set @SEQUENCE = @SEQUENCE + @DELTA;

          set @SQL = N'select @GLFISCALYEARID = [ID] from dbo.[GLFISCALYEAR] where [YEARSEQUENCE] = @SEQUENCE;';

          exec sp_executesql @SQL, N'@GLFISCALYEARID uniqueidentifier output, @SEQUENCE integer', @GLFISCALYEARID = @GLFISCALYEARID output, @SEQUENCE = @SEQUENCE;

          if @GLFISCALYEARID is not null
            begin
              -- 3. get the start and end dates of that fiscal year

              set @SQL = N'select @STARTDATE = [STARTDATE] from dbo.[GLFISCALPERIOD] where [GLFISCALYEARID] = @GLFISCALYEARID and [SEQUENCE] = 1;';

              exec sp_executesql @SQL, N'@STARTDATE date output, @GLFISCALYEARID uniqueidentifier', @STARTDATE = @STARTDATE output, @GLFISCALYEARID = @GLFISCALYEARID;

              set @SQL = N'select @ENDDATE = [ENDDATE] from dbo.[GLFISCALPERIOD] where [GLFISCALYEARID] = @GLFISCALYEARID and [SEQUENCE] = (select max([SEQUENCE]) from dbo.[GLFISCALPERIOD] where [GLFISCALYEARID] = @GLFISCALYEARID);';

              exec sp_executesql @SQL, N'@ENDDATE date output, @GLFISCALYEARID uniqueidentifier', @ENDDATE = @ENDDATE output, @GLFISCALYEARID = @GLFISCALYEARID;

              update @DATES set [STARTDATE] = @STARTDATE, [ENDDATE] = @ENDDATE, [ERRORCODE] = case when @STARTDATE is null or @ENDDATE is null then 4 else 0 end;
            end
          else
            update @DATES set [ERRORCODE] = 3;
        end
      else
        update @DATES set [ERRORCODE] = 2;
    end
  else
    update @DATES set [ERRORCODE] = 1;

  select [STARTDATE], [ENDDATE], [ERRORCODE] from @DATES;

  return 0;
end