USP_DATALIST_SEGMENTAVERAGEGIFTAMOUNT_COMBINED

CREATE procedure dbo.[USP_DATALIST_SEGMENTAVERAGEGIFTAMOUNT_COMBINED]
(
  @SEGMENTID uniqueidentifier = null,
  @SEGMENTID2 uniqueidentifier = null,
  @SEGMENTID3 uniqueidentifier = null,
  @NUMYEARS int = 5,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SQLWITHJOINHINT nvarchar(max);
  declare @SEGMENTNAME nvarchar(100);
  declare @RECORDTYPEID uniqueidentifier;
  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  declare @SEGMENT1RESULTS table (
    [SEGMENTNAME] nvarchar(100), 
    [YEAR] int, 
    [AVERAGEGIFTAMOUNT] money,
    [CURRENCYISOCURRENCYCODE] nvarchar(6), 
    [CURRENCYDECIMALDIGITS] tinyint, 
    [CURRENCYSYMBOL] nvarchar(10), 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  declare @SEGMENT2RESULTS table (
    [SEGMENTNAME] nvarchar(100), 
    [YEAR] int, 
    [AVERAGEGIFTAMOUNT] money,
    [CURRENCYISOCURRENCYCODE] nvarchar(6), 
    [CURRENCYDECIMALDIGITS] tinyint, 
    [CURRENCYSYMBOL] nvarchar(10), 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  declare @SEGMENT3RESULTS table (
    [SEGMENTNAME] nvarchar(100), 
    [YEAR] int, 
    [AVERAGEGIFTAMOUNT] money,
    [CURRENCYISOCURRENCYCODE] nvarchar(6), 
    [CURRENCYDECIMALDIGITS] tinyint, 
    [CURRENCYSYMBOL] nvarchar(10), 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  declare @RESULTSTABLE table (
    [SEGMENTNAME] nvarchar(100), 
    [YEAR] int, 
    [AVERAGEGIFTAMOUNT] money,
    [CURRENCYISOCURRENCYCODE] nvarchar(6), 
    [CURRENCYDECIMALDIGITS] tinyint, 
    [CURRENCYSYMBOL] nvarchar(10), 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint,
    [SEGMENTORDER] int
  );

  declare @NUMBEROFYEARSTOINSERT integer = @NUMYEARS - 1;
  declare @INSERTYEAR bit = 0;
  declare @YEAR int;
  declare @YEARTABLE table (
  [GIFTYEAR] integer
  )

  /* Combine the results of the given segments */
  if not @SEGMENTID is null
    begin
      exec dbo.[USP_SEGMENTAVERAGEGIFTAMOUNT_GETSQL] @SEGMENTID, @NUMYEARS, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @CURRENCYCODE, @SQL output, @SEGMENTNAME output, @RECORDTYPEID output, @CURRENCYISOCURRENCYCODE output, @CURRENCYDECIMALDIGITS output, @CURRENCYSYMBOL output, @CURRENCYSYMBOLDISPLAYSETTINGCODE output;
      if @SQL is not null
      begin
        --Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.

        begin try
          set @SQLWITHJOINHINT = @SQL + char(13) + 'option (hash join, merge join);';
            insert into @SEGMENT1RESULTS
            exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
              @SEGMENTID = @SEGMENTID, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
              @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
              @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
        end try
        begin catch
          if ERROR_NUMBER() = 8622
            insert into @SEGMENT1RESULTS
              exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
                @SEGMENTID = @SEGMENTID, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
                @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
                @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
                @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
          else
            begin
              exec dbo.[USP_RAISE_ERROR];
            end
        end catch
        set @SQL = null;
      end      
      /* Insert correct number of blank years into the 1st segment table depending on @NUMYEARS.  So if a gift was not given to the segment in 
         2007, insert 2007 with 0 for gifts. */
      begin

        set @NUMBEROFYEARSTOINSERT = @NUMYEARS - 1;
        set @INSERTYEAR = 0;

        while @NUMBEROFYEARSTOINSERT >= 0
          begin

            set @YEAR = year(getdate()) - @NUMBEROFYEARSTOINSERT;
            set @INSERTYEAR =  case when exists (select 1 from @SEGMENT1RESULTS AS [RESULTS] where [RESULTS].[YEAR] = @YEAR) then 0 else 1 end;

            -- Insert blank year if no gifts given in that year.  

            if @INSERTYEAR = 1
              begin
                insert into @YEARTABLE
                  select @YEAR;
              end

            set @NUMBEROFYEARSTOINSERT = @NUMBEROFYEARSTOINSERT - 1;
          end

        insert into @SEGMENT1RESULTS
        select
          @SEGMENTNAME,
          [GIFTYEAR],
          0,
          @CURRENCYISOCURRENCYCODE,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE
        from @YEARTABLE;
      end

      insert into @RESULTSTABLE
        select
          [SEGMENTNAME],
          [YEAR], 
          [AVERAGEGIFTAMOUNT],
          [CURRENCYISOCURRENCYCODE], 
          [CURRENCYDECIMALDIGITS], 
          [CURRENCYSYMBOL], 
          [CURRENCYSYMBOLDISPLAYSETTINGCODE],
          1 -- segment order

        from @SEGMENT1RESULTS;
    end

    if not @SEGMENTID2 is null
    begin
      exec dbo.[USP_SEGMENTAVERAGEGIFTAMOUNT_GETSQL] @SEGMENTID2, @NUMYEARS, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @CURRENCYCODE, @SQL output, @SEGMENTNAME output, @RECORDTYPEID output, @CURRENCYISOCURRENCYCODE output, @CURRENCYDECIMALDIGITS output, @CURRENCYSYMBOL output, @CURRENCYSYMBOLDISPLAYSETTINGCODE output;
      if @SQL is not null
      begin
        --Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.

        begin try
          set @SQLWITHJOINHINT = @SQL + char(13) + 'option (hash join, merge join);';

            insert into @SEGMENT2RESULTS
            exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
              @SEGMENTID = @SEGMENTID2, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
              @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
              @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
        end try
        begin catch
          if ERROR_NUMBER() = 8622
            insert into @SEGMENT2RESULTS
              exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
                @SEGMENTID = @SEGMENTID2, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
                @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
                @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
                @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
          else
            begin
              exec dbo.[USP_RAISE_ERROR];
            end
        end catch
        set @SQL = null;
      end

      /* Insert correct number of blank years into the 2nd segment table depending on @NUMYEARS.  So if a gift was not given to the segment in 
         2007, insert 2007 with 0 for gifts. */
      begin

        set @NUMBEROFYEARSTOINSERT = @NUMYEARS - 1;
        set @INSERTYEAR = 0;
        delete from @YEARTABLE;

        while @NUMBEROFYEARSTOINSERT >= 0
          begin

          set @YEAR = year(getdate()) - @NUMBEROFYEARSTOINSERT;
          set @INSERTYEAR =  case when exists (select 1 from @SEGMENT2RESULTS AS [RESULTS] where [RESULTS].[YEAR] = @YEAR) then 0 else 1 end;

          -- Insert blank year if no gifts given in that year.  

          if @INSERTYEAR = 1
            begin
              insert into @YEARTABLE
                select @YEAR;
            end

          set @NUMBEROFYEARSTOINSERT = @NUMBEROFYEARSTOINSERT - 1;
        end

        insert into @SEGMENT2RESULTS
        select
          @SEGMENTNAME,
          [GIFTYEAR],
          0,
          @CURRENCYISOCURRENCYCODE,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE
        from @YEARTABLE;
      end

      insert into @RESULTSTABLE
        select
          [SEGMENTNAME],
          [YEAR], 
          [AVERAGEGIFTAMOUNT],
          [CURRENCYISOCURRENCYCODE], 
          [CURRENCYDECIMALDIGITS], 
          [CURRENCYSYMBOL], 
          [CURRENCYSYMBOLDISPLAYSETTINGCODE],
          2 -- segment order

        from @SEGMENT2RESULTS;

    end

  if not @SEGMENTID3 is null
    begin
      exec dbo.[USP_SEGMENTAVERAGEGIFTAMOUNT_GETSQL] @SEGMENTID3, @NUMYEARS, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @CURRENCYCODE, @SQL output, @SEGMENTNAME output, @RECORDTYPEID output, @CURRENCYISOCURRENCYCODE output, @CURRENCYDECIMALDIGITS output, @CURRENCYSYMBOL output, @CURRENCYSYMBOLDISPLAYSETTINGCODE output;
      if @SQL is not null
      begin
        --Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.

        begin try
          set @SQLWITHJOINHINT = @SQL + char(13) + 'option (hash join, merge join);';
            insert into @SEGMENT3RESULTS
            exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
              @SEGMENTID = @SEGMENTID3, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
              @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
              @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
        end try
        begin catch
          if ERROR_NUMBER() = 8622
            insert into @SEGMENT3RESULTS
              exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
                @SEGMENTID = @SEGMENTID3, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
                @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
                @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
                @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
          else
            begin
              exec dbo.[USP_RAISE_ERROR];
            end
        end catch
        set @SQL = null;
      end
      /* Insert correct number of blank years into the 3rd segment table depending on @NUMYEARS.  So if a gift was not given to the segment in 
         2007, insert 2007 with 0 for gifts. */
      begin

        set @NUMBEROFYEARSTOINSERT = @NUMYEARS - 1;
        set @INSERTYEAR = 0;
        delete from @YEARTABLE;

        while @NUMBEROFYEARSTOINSERT >= 0
          begin

            set @YEAR = year(getdate()) - @NUMBEROFYEARSTOINSERT;
            set @INSERTYEAR =  case when exists (select 1 from @SEGMENT3RESULTS AS [RESULTS] where [RESULTS].[YEAR] = @YEAR) then 0 else 1 end;

            -- Insert blank year if no gifts given in that year.  

            if @INSERTYEAR = 1
              begin
                insert into @YEARTABLE
                  select @YEAR;
              end

            set @NUMBEROFYEARSTOINSERT = @NUMBEROFYEARSTOINSERT - 1;
          end

        insert into @SEGMENT3RESULTS
        select
          @SEGMENTNAME,
          [GIFTYEAR],
          0,
          @CURRENCYISOCURRENCYCODE,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE
        from @YEARTABLE;
      end

    insert into @RESULTSTABLE
      select
        [SEGMENTNAME],
        [YEAR], 
        [AVERAGEGIFTAMOUNT],
        [CURRENCYISOCURRENCYCODE], 
        [CURRENCYDECIMALDIGITS], 
        [CURRENCYSYMBOL], 
        [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        3 -- segment order

      from @SEGMENT3RESULTS;

  end

  select
    [SEGMENTNAME],
    [YEAR], 
    [AVERAGEGIFTAMOUNT],
    [CURRENCYISOCURRENCYCODE], 
    [CURRENCYDECIMALDIGITS], 
    [CURRENCYSYMBOL], 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from @RESULTSTABLE
  order by [SEGMENTORDER], [YEAR] asc;

  return 0;