USP_DATALIST_MKTASKLADDERRESPONSE

CREATE procedure dbo.[USP_DATALIST_MKTASKLADDERRESPONSE]
(
  @SELECTIONID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @EFFORTID uniqueidentifier = null,
  @FILTERTYPECODE int = 0,
  @CURRENCYCODE tinyint = 1
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @FROMSQL nvarchar(max);
  declare @WITHBLOCKSQL nvarchar(max);
  declare @GIFTIDSET nvarchar(128);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SELECTIONNAME nvarchar(300);
  declare @MAILINGDATATABLENAME nvarchar(128);
  declare @LISTMATCHBACKTABLE nvarchar(128);
  declare @LISTIDSQL nvarchar(128);
  declare @SELECTSQL nvarchar(max);
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  /* When the ask ladder is in a different currency than organization, if the conversion rates are not set up 
     perfectly, there could be conversion issues with matching gifts to the proper ask value.  So the conversion
     error bar was introduced to match ask ladders that are close by a percentage, like matching 4.94 with 5. */
  declare @CONVERSIONERRORBAR decimal(3,2) = .02;
  declare @USEERRORBAR bit;

  declare @ENTRYAMOUNTTABLE table (
    [RECORDSOURCEID] uniqueidentifier, 
    [OBJECTKEY] nvarchar(128), 
    [ENTRYAMOUNT] nvarchar(255)
  );

  declare @ALLTABLE table (
    [ASKLADDERITEMID] uniqueidentifier,
    [ASK1] int,
    [ASK2] int,
    [ASK3] int,
    [ASK4] int,
    [ASK5] int,
    [TOTAL] int
  );

  if @SELECTIONID is not null 
  begin
    --Get returned parameter translations

    select @SELECTIONNAME = [NAME]
    from dbo.[IDSETREGISTER]
    where [ID] = @SELECTIONID;
  end;

  --Build Segmentation cursor

  declare @SEGMENTATIONCURSOR cursor;                    

  if @FILTERTYPECODE = 0 begin
  set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) + 
         '  from dbo.[MKTSEGMENTATIONACTIVATE]' + char(13) +                                  
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4;' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end

  if @FILTERTYPECODE = 1 begin
    set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [IDSET].[ID]' + char(13) + 
         '  from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as [IDSET]' + char(13) + 
         '  inner join dbo.[MKTSEGMENTATIONACTIVATE] on  [IDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [IDSET].[ID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4;' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end;

  if @FILTERTYPECODE = 2 begin
    set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) + 
         '  from dbo.[MKTSEGMENTATIONACTIVATE]' + char(13) +                                  
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4 and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = ''' + convert(nvarchar(36), @EFFORTID) + ''';' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end

  -- might as well leave UFN_SITEALLOWEDFORUSER up there, to cut down on the number of rows the real site security has to check

  -- they still need to be checked, though, because of the link between site security and the role in which access to a datalist is granted


  exec sp_executesql @SQL, N'@SEGMENTATIONCURSOR cursor output, @RECORDSOURCEID uniqueidentifier', @SEGMENTATIONCURSOR = @SEGMENTATIONCURSOR output, @RECORDSOURCEID = @RECORDSOURCEID; 

  --Loop through each Mailing

  fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;

  while (@@FETCH_STATUS = 0)
  begin
    if ( -- check site security

       select count(*) 
       from (select [SITEID]
             from dbo.[MKTSEGMENTATION]
             where [ID] = @SEGMENTATIONID) 
       as [SEGMENTATIONSITE]
       where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SEGMENTATIONSITE].[SITEID] or (SITEID is null and [SEGMENTATIONSITE].[SITEID] is null)))
       ) > 0
    begin
    set @MAILINGDATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

    --Loop through each record source for the mailing

    declare RECORDSOURCECURSOR cursor local fast_forward for
      select distinct [RECORDSOURCEID]
      from dbo.[MKTSEGMENTATIONACTIVATE]
      where [SEGMENTATIONID] = @SEGMENTATIONID;

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;

    while(@@FETCH_STATUS = 0)
    begin
      --Get the entry amount column to display later

      insert into @ENTRYAMOUNTTABLE
      select distinct
        @RECORDSOURCEID,
        [MF].[OBJECTKEY],
        [MF].[PARENTTYPE] + ' \ ' +  [MF].[DISPLAYNAME]
      from dbo.[UFN_MKTASKLADDER_GETMONEYFIELDS](@RECORDSOURCEID) [MF];

      --Get the gifts for this mailing on this record source

      select
        @GIFTIDSET = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([NORMALGIFTIDSETREGISTERID]),
        @LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([RECORDSOURCEID])
      from dbo.[MKTSEGMENTATIONACTIVATE] 
      where [SEGMENTATIONID] = @SEGMENTATIONID
      and [RECORDSOURCEID] = @RECORDSOURCEID;

      --Make sure all the ask ladder items are in the table

      set @SQL = 'select distinct [MKTASKLADDERITEM].[ID], 0, 0, 0, 0, 0, 0' + char(13) +
           'from dbo.[MKTASKLADDERITEM]' + char(13) +
           'inner join dbo.[' + @MAILINGDATATABLENAME + '] as [MAILINGDATA] on [MKTASKLADDERITEM].[ASKLADDERID] = [MAILINGDATA].[ASKLADDERID]';
      insert into @ALLTABLE
      exec (@SQL);

      set @FROMSQL = '  from dbo.[MKTASKLADDERITEM] as [ITEM]' + char(13) +
                     '  inner join dbo.[' + @MAILINGDATATABLENAME + '] as [MAILINGDATA] on [ITEM].[ASKLADDERID] = [MAILINGDATA].[ASKLADDERID] and [MAILINGDATA].[ENTRYAMOUNT] between [ITEM].[MINIMUMENTRYAMOUNT] and ([ITEM].[NEXTMINIMUMENTRYAMOUNT] - 0.0001)' + char(13) +
                     '  inner join dbo.' + @GIFTIDSET + ' as [GIFTS] on cast([MAILINGDATA].[DONORID] as varchar(36)) = cast([GIFTS].[DONORID] as varchar(36))' + char(13) +
                     '  where [MAILINGDATA].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID';

      -- Use conversion error bars +/- % for matching gifts to ask ladder when effort currency is different than organization currency.

      select 
        @USEERRORBAR = case when [BASECURRENCYID] <> @ORGANIZATIONCURRENCYID then 1 else 0 end
      from dbo.[MKTSEGMENTATION]
      where [ID] = @SEGMENTATIONID;

      if @USEERRORBAR = 1
        begin
          set @WITHBLOCKSQL = 'with [ASKLADDERMATCHES] as (' + char(13) +
                              '  select' + char(13) +
                              '    [ITEM].[ID] as [ITEMID],' + char(13) +
                              '    [GIFTS].[AMOUNT] as [GIFTAMOUNT],' + char(13) +
                              '    dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE1],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) as [ASK1],' + char(13) +
                              '    dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE2],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) as [ASK2],' + char(13) +
                              '    dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE3],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) as [ASK3],' + char(13) +
                              '    dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE4],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) as [ASK4],' + char(13) +
                              '    dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE5],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) as [ASK5],' + char(13) +
                              '    abs([GIFTS].[AMOUNT] - dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE1],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT])) as [ASK1DIFFERENCE],' + char(13) +
                              '    abs([GIFTS].[AMOUNT] - dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE2],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT])) as [ASK2DIFFERENCE],' + char(13) +
                              '    abs([GIFTS].[AMOUNT] - dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE3],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT])) as [ASK3DIFFERENCE],' + char(13) +
                              '    abs([GIFTS].[AMOUNT] - dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE4],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT])) as [ASK4DIFFERENCE],' + char(13) +
                              '    abs([GIFTS].[AMOUNT] - dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE5],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT])) as [ASK5DIFFERENCE],' + char(13) +
                              '    1 as [TOTAL]' + char(13);

          set @SELECTSQL = 'select' + char(13) +
                           '  [ITEMID],' + char(13) +
                           --Match when gift amount is close to the ask ladder amount, and the is closest to the ask value compared to the other ask values.

                           '  case when [GIFTAMOUNT] between ((1 - @CONVERSIONERRORBAR) * [ASK1]) and ((1 + @CONVERSIONERRORBAR) * [ASK1]) and [ASK1DIFFERENCE] <= [ASK2DIFFERENCE] then 1 else 0 end,' + char(13) +
                           '  case when [GIFTAMOUNT] between ((1 - @CONVERSIONERRORBAR) * [ASK2]) and ((1 + @CONVERSIONERRORBAR) * [ASK2]) and [ASK2DIFFERENCE] < [ASK1DIFFERENCE] and [ASK2DIFFERENCE] <= [ASK3DIFFERENCE] then 1 else 0 end,' + char(13) +
                           '  case when [GIFTAMOUNT] between ((1 - @CONVERSIONERRORBAR) * [ASK3]) and ((1 + @CONVERSIONERRORBAR) * [ASK3]) and [ASK3DIFFERENCE] < [ASK2DIFFERENCE] and [ASK3DIFFERENCE] <= [ASK4DIFFERENCE] then 1 else 0 end,' + char(13) +
                           '  case when [GIFTAMOUNT] between ((1 - @CONVERSIONERRORBAR) * [ASK4]) and ((1 + @CONVERSIONERRORBAR) * [ASK4]) and [ASK4DIFFERENCE] < [ASK3DIFFERENCE] and [ASK4DIFFERENCE] <= [ASK5DIFFERENCE] then 1 else 0 end,' + char(13) +
                           '  case when [GIFTAMOUNT] between ((1 - @CONVERSIONERRORBAR) * [ASK5]) and ((1 + @CONVERSIONERRORBAR) * [ASK5]) and [ASK5DIFFERENCE] < [ASK4DIFFERENCE] then 1 else 0 end,' + char(13) +
                           '  [TOTAL]' + char(13) +
                           'from [ASKLADDERMATCHES]';
        end
      else
        set @SELECTSQL =  'select ' + char(13) +
                          '  [ITEM].[ID], ' + char(13) +
                          '  (case when [GIFTS].[AMOUNT] = dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE1],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) then 1 else 0 end),' + char(13) +
                          '  (case when [GIFTS].[AMOUNT] = dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE2],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) then 1 else 0 end),' + char(13) +
                          '  (case when [GIFTS].[AMOUNT] = dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE3],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) then 1 else 0 end),' + char(13) +
                          '  (case when [GIFTS].[AMOUNT] = dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE4],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) then 1 else 0 end),' + char(13) +
                          '  (case when [GIFTS].[AMOUNT] = dbo.[UFN_MKTASKLADDER_CALCVALUE]([ITEM].[TYPECODE],[MAILINGDATA].[ENTRYAMOUNT],[ITEM].[ITEMVALUE5],[ITEM].[ROUNDTOAMOUNT],[ITEM].[MINIMUMENTRYAMOUNT]) then 1 else 0 end),' + char(13) +
                          '  1' + char(13);

      --Insert all non list gifts into their buckets

      if @USEERRORBAR = 1
        set @SQL = @WITHBLOCKSQL + @FROMSQL + char(13) + ')' + char(13) + @SELECTSQL + ';';
      else
        set @SQL = @SELECTSQL + @FROMSQL + ';';

      insert into @ALLTABLE
      exec sp_executesql @SQL, N'@RECORDSOURCEID uniqueidentifier, @CONVERSIONERRORBAR decimal(3,2)', @RECORDSOURCEID = @RECORDSOURCEID, @CONVERSIONERRORBAR = @CONVERSIONERRORBAR;

      --Check if the mailing contains any lists...

      if exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID)
      begin
        --See if we need to cast the person ID from the list matchback table(s) in the list joins...

        select @LISTIDSQL = (case when [DATA_TYPE] = 'uniqueidentifier' then '[LISTDONORS].[ID]' else 'cast([LISTDONORS].[ID] as varchar(36))' end)
        from [INFORMATION_SCHEMA].[COLUMNS]
        where [TABLE_SCHEMA] = 'dbo'
        and [TABLE_NAME] = @MAILINGDATATABLENAME
        and [COLUMN_NAME] = 'DONORID';

        set @FROMSQL = '  from dbo.[MKTASKLADDERITEM] as [ITEM]' + char(13) +
                       '  inner join dbo.[' + @MAILINGDATATABLENAME + '] as [MAILINGDATA] on [ITEM].[ASKLADDERID] = [MAILINGDATA].[ASKLADDERID] and isnull([MAILINGDATA].[ENTRYAMOUNT],0) between [ITEM].[MINIMUMENTRYAMOUNT] and ([ITEM].[NEXTMINIMUMENTRYAMOUNT] - 0.0001)' + char(13) +
                       '  inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MAILINGDATA].[SEGMENTID]' + char(13) +
                       '  inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + @LISTIDSQL + ' = [MAILINGDATA].[DONORID]' + char(13) +
                       '  inner join dbo.' + @GIFTIDSET + ' as [GIFTS] on [LISTDONORS].[GIFTID] = [GIFTS].[ID]' + char(13) +
                       '  where [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID';

        --Insert all non list gifts into their buckets

        if @USEERRORBAR = 1
          set @SQL = @WITHBLOCKSQL + char(13) + @FROMSQL + ')' + char(13) + @SELECTSQL + ';';
        else
          set @SQL = @SELECTSQL + @FROMSQL + ';';

        insert into @ALLTABLE
        exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @CONVERSIONERRORBAR decimal(3,2)', @SEGMENTATIONID = @SEGMENTATIONID, @CONVERSIONERRORBAR = @CONVERSIONERRORBAR;
      end

      fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
    end;

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;
    end;

  fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;
  end;

  close @SEGMENTATIONCURSOR;
  deallocate @SEGMENTATIONCURSOR;

  declare @SUM table (
    [ASKLADDERITEMID] uniqueidentifier,
    [COUNT1] int,
    [COUNT2] int,
    [COUNT3] int,
    [COUNT4] int,
    [COUNT5] int,
    [COUNTOTHER] int,
    [TOTAL] int);

  insert into @SUM
  select 
    [ASKLADDERITEMID], 
    sum([ASK1]) as [COUNT1], 
    sum([ASK2]) as [COUNT2], 
    sum([ASK3]) as [COUNT3], 
    sum([ASK4]) as [COUNT4], 
    sum([ASK5]) as [COUNT5], 
    (sum([TOTAL]) - sum([ASK1]) - sum([ASK2]) - sum([ASK3]) - sum([ASK4]) - sum([ASK5])) as [COUNTOTHER],
    sum([TOTAL]) as [TOTAL]
  from @ALLTABLE
  group by [ASKLADDERITEMID];

  --Display

  if exists (select [ASKLADDERITEMID] from @SUM)
  begin
    select 
      [MKTASKLADDER].[NAME],
      [EAT].[ENTRYAMOUNT],
      case when @CURRENCYCODE = 1 then [ITEM].[ORGANIZATIONMINIMUMENTRYAMOUNT] else [ITEM].[MINIMUMENTRYAMOUNT] end as [LOW],
      [ITEM].[NEXTMINIMUMENTRYAMOUNT] as [HIGH],
      [ITEM].[TYPE],
      --If type is multiply (1), then show the base amount because we do not want to convert the multiplier.

      case when @CURRENCYCODE = 1  and [ITEM].[TYPECODE] <> 1 then [ITEM].[ORGANIZATIONITEMVALUE1] else [ITEM].[ITEMVALUE1] end as [ASK1],
      [SUM].[COUNT1] as [COUNT1],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNT1] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENT1],
      case when @CURRENCYCODE = 1 and [ITEM].[TYPECODE] <> 1 then [ITEM].[ORGANIZATIONITEMVALUE2] else [ITEM].[ITEMVALUE2] end as [ASK2],
      [SUM].[COUNT2] as [COUNT2],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNT2] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENT2],
      case when @CURRENCYCODE = 1 and [ITEM].[TYPECODE] <> 1 then [ITEM].[ORGANIZATIONITEMVALUE3] else [ITEM].[ITEMVALUE3] end as [ASK3],
      [SUM].[COUNT3] as [COUNT3],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNT3] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENT3],
      case when @CURRENCYCODE = 1 and [ITEM].[TYPECODE] <> 1 then [ITEM].[ORGANIZATIONITEMVALUE4] else [ITEM].[ITEMVALUE4] end as [ASK4],
      [SUM].[COUNT4] as [COUNT4],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNT4] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENT4],
      case when @CURRENCYCODE = 1 and [ITEM].[TYPECODE] <> 1 then [ITEM].[ORGANIZATIONITEMVALUE5] else [ITEM].[ITEMVALUE5] end as [ASK5],
      [SUM].[COUNT5] as [COUNT5],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNT5] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENT5],
      [ITEM].[WRITEINTEXT] as [PROMPT],
      [SUM].[COUNTOTHER] as [COUNTOTHER],
      (case when [SUM].[TOTAL] > 0 then cast([SUM].[COUNTOTHER] as decimal(20,4))/cast([SUM].[TOTAL] as decimal(20,4)) else 0 end) as [PERCENTOTHER],
      [SUM].[TOTAL] as [TOTAL],
      @SELECTIONNAME as [SELECTIONNAME],
      [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
      [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
      [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
      [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
      case when [MKTASKLADDER].[BASECURRENCYID] <> @ORGANIZATIONCURRENCYID then 1 else 0 end as [ROUNDINGUSED]
    from @SUM as [SUM]
    inner join dbo.[MKTASKLADDERITEM] as [ITEM] on [ITEM].[ID] = [SUM].[ASKLADDERITEMID]
    inner join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [ITEM].[ASKLADDERID]
    left join (select distinct [RECORDSOURCEID], [OBJECTKEY], [ENTRYAMOUNT] from @ENTRYAMOUNTTABLE) as [EAT] on [EAT].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY] and [EAT].[RECORDSOURCEID] = [MKTASKLADDER].[RECORDSOURCEID]
    inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then @ORGANIZATIONCURRENCYID else [ITEM].[BASECURRENCYID] end
    order by [MKTASKLADDER].[NAME], [ITEM].[MINIMUMENTRYAMOUNT];
  end
  else
  begin
    select 
      null as [NAME],          -- name

      null as [ENTRYAMOUNT],          -- entryAmount

      0 as [LOW],             -- low

      0 as [HIGH],             -- high

      null as [TYPE],          -- type

      0 as [ASK1],             -- ask1

      0 as [COUNT1],             -- count1

      0 as [PERCENT1],             -- percent1

      0 as [ASK2],             -- ask2

      0 as [COUNT2],             -- count2

      0 as [PERCENT2],             -- percent2

      0 as [ASK3],             -- ask3

      0 as [COUNT3],             -- count3

      0 as [PERCENT3],             -- percent3

      0 as [ASK4],             -- ask4

      0 as [COUNT4],             -- count4

      0 as [PERCENT4],             -- percent4

      0 as [ASK5],             -- ask5

      0 as [COUNT5],             -- count5

      0 as [PERCENT5],             -- percent5

      0 as [PROMPT],             -- prompt

      0 as [COUNTOTHER],             -- countOther

      0 as [PERCENTOTHER],             -- percentOther

      0 as [TOTAL],             -- total

      @SELECTIONNAME  as [SELECTIONNAME],-- selectionname

      '' as [CURRENCYISOCURRENCYCODE],            -- currencyIsoCurrencyCode

      0 as [CURRENCYDECIMALDIGITS],             -- CurrencyDecimalDigits

      '' as [CURRENCYSYMBOL],            -- CurrencySymbol

      0 as [CURRENCYSYMBOLDISPLAYSETTINGCODE],             -- CurrencySymbolDisplaySettingCode

      0 as [ROUNDINGUSED];             -- RoundingUsed

  end

  return 0;