USP_RE7IDSMARTFIELD_GIFTSPLIT

create procedure dbo.[USP_RE7IDSMARTFIELD_GIFTSPLIT]
( 
  @AGGREGATETYPE tinyint,
  @GIFTSELECTION uniqueidentifier,
  @ASOF datetime,
  @INCLUDE tinyint = 0
) 
as 
  set nocount on;

  if not exists (select 1 from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
  begin
  raiserror('The gift selection for this smart field no longer exists.', 13, 1);
  return 1;
  end

  declare @IDSET nvarchar(128);
  declare @SQL nvarchar(max);
  set @IDSET = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME] (@GIFTSELECTION);

  declare @AMOUNTFIELD sysname;
  declare @ORDERBYDIRECTION nvarchar(4);
  declare @MATH nvarchar(100);

  if @AGGREGATETYPE in (2,3,4,5)
  begin
  select 
    @AMOUNTFIELD = 
    case
    when @AGGREGATETYPE in (3, 5) then '[GIFT].[SPLITAMOUNT]'
    when @AGGREGATETYPE in (2, 4) then '[GIFT].[AMOUNT]'
    end,
    @ORDERBYDIRECTION = 
    case
    when @AGGREGATETYPE in (2, 3) then 'asc'
    when @AGGREGATETYPE in (4, 5) then 'desc'
    end      

  --Full Refresh 

  if @ASOF is null or @ASOF < (select [DATECHANGED] from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
  begin
    set @SQL = '';

    if @INCLUDE in (1, 2) -- soft only or both

        set @SQL = 'with [GIFTLEVELIDS] ([ID]) as ' + char(13) +
        '( ' + char(13) +
        'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
        '), ' + char(13);

    if @INCLUDE = 0
    set @SQL = @SQL + 'with ';

    set @SQL = @SQL +
    '[AMOUNTS] ([CONSTITUENTID], [AMOUNT], [DATE], [DATEADDED], [GIFTSPLITID]) as' + char(13) + 
    '('  + char(13);

      if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
        'select [CONSTITUENT_SYSTEMID], '+ @AMOUNTFIELD + ', [DATE], [DATEADDED], [GIFTSPLITID]' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFT] [GIFT]' + char(13) + 
        '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

    if @INCLUDE = 2 --both so we need to union

    set @SQL = @SQL + 
      'union' + char(13);

      if @INCLUDE in (1, 2) -- soft only or both

        set @SQL = @SQL + 
        'select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID] ' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) + 
              '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[GIFTID]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL +
    '), ' + char(13) + 
        '[CONSTITRANKS] ([CONSTITUENT_LOCALID], [RANK], [GIFTSPLITID]) as' + char(13) + 
        '(' + char(13) + 
        '  select [GIFT].[CONSTITUENTID], dense_rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[AMOUNT] ' + @ORDERBYDIRECTION + ', [GIFT].[DATE] desc, [GIFT].[DATEADDED] desc, [GIFT].[GIFTSPLITID] desc) [RANK], [GIFTSPLITID]' + char(13) + 
        '  from [AMOUNTS] [GIFT]' + char(13) + 
        ')' + char(13) + 
        'select [CONSTITUENT].[LOCALID], isnull([CONSTITRANKS].[GIFTSPLITID],0)' + char(13) + 
        'from dbo.[V_QUERY_RE7_CONSTITUENT] [CONSTITUENT]' + char(13) + 
        'left join [CONSTITRANKS] on [CONSTITRANKS].[CONSTITUENT_LOCALID] = [CONSTITUENT].[LOCALID]' + char(13) + 
        'where isnull([CONSTITRANKS].[RANK], 1) = 1;' + char(13); 
    exec sp_executesql @SQL;

  end
  --Incremental Refresh

  else
  begin
    set @SQL = '';

    --Find all affected constituents

    if @INCLUDE in (1,2)--soft or both

      set @SQL = @SQL + 
        'with [GIFTLEVELIDS] ([ID]) as ' + char(13) +
        '( ' + char(13) +
        'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
      '), ' + char(13);

    if @INCLUDE = 0 --hard only

    set @SQL = @SQL + 'with ';

    set @SQL = @SQL + 
      '[CONSTITS] ([CONSTITUENT_LOCALID]) as ' + char(13) + 
    '(' + char(13);

    if @INCLUDE in (0,2) --hard or both

      set @SQL = @SQL + 
        '  select isnull([VIEW].[CONSTITUENT_SYSTEMID],[RSD].[CONSTITUENT_LOCALID])' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFT] [VIEW]' + char(13) + 
        '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [VIEW].[GIFTSPLITID]' + char(13) + 
        '  full join [RE7_REVENUESPLIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_SYSTEMID]' + char(13) + 
        '  group by [VIEW].[CONSTITUENT_SYSTEMID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) + 
        '  having max([VIEW].[DATELASTCHANGED]) > @ASOF' + char(13) + 
        '  or max([DELETEDDATE]) > @ASOF' + char(13);

    if @INCLUDE = 2 --both so we need to union

    set @SQL = @SQL + 'union' + char(13);

    if @INCLUDE in (1,2)
      set @SQL = @SQL + 
        '    select isnull([VIEW].[CONSTITUENT_LOCALID],[RSD].[CONSTITUENT_LOCALID])' + char(13) +
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [VIEW]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [VIEW].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
        '  full join [RE7_SOFTCREDIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_LOCALID]' + char(13) +
        '  group by [VIEW].[CONSTITUENT_LOCALID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) +
        '  having (max([VIEW].[REVENUE_DATECHANGED]) > @ASOF) or (max([RSD].[DELETEDDATE]) > @ASOF)' + char(13);

  set @SQL = @SQL +
    '), ' + char(13) +
    '[AMOUNTS] ([CONSTITUENTID], [AMOUNT], [DATE], [DATEADDED], [GIFTSPLITID]) as ' + char(13) +
    '(' + char(13);

    --Calculate new values with available data

    if @INCLUDE in (0, 2) --hard only or both

        set @SQL = @SQL +
          '  select [GIFT].[CONSTITUENT_SYSTEMID], '+ @AMOUNTFIELD + ', [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID]' + char(13) + 
          '  from [CONSTITS]' + char(13) + 
          '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [CONSTITS].[CONSTITUENT_LOCALID] = [GIFT].[CONSTITUENT_SYSTEMID]' + char(13) + 
          '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

    if @INCLUDE = 2 --both so we must union

    set @SQL = @SQL + 'union' + char(13);

      if @INCLUDE in (1, 2) -- soft only or both

    set @SQL = @SQL +
        'select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID]' + char(13) + 
        '  from [CONSTITS]' + char(13) + 
        '  inner join dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC] on [CONSTITS].[CONSTITUENT_LOCALID] = [SC].[CONSTITUENT_LOCALID]' + char(13) + 
                '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[GIFTID]' + char(13) + 
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

    set @SQL = @SQL + '),' + char(13);

    --Return affected constituents with new values

    set @SQL = @SQL + 
      '[CONSTITRANKS] ([CONSTITUENT_LOCALID], [RANK], [GIFTSPLITID]) as' + char(13) + 
      '(' + char(13) + 
      '  select [GIFT].[CONSTITUENTID], dense_rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[AMOUNT] ' + @ORDERBYDIRECTION + ', [GIFT].[DATE] desc, [GIFT].[DATEADDED] desc, [GIFT].[GIFTSPLITID] desc) [RANK], [GIFTSPLITID]' + char(13) + 
      '  from [AMOUNTS] [GIFT]' + char(13) + 
      ')' + char(13) + 
    'select [CONSTITS].[CONSTITUENT_LOCALID], isnull([CONSTITRANKS].[GIFTSPLITID],0)' + char(13) + 
    'from [CONSTITS]' + char(13) + 
    'left join [CONSTITRANKS] on [CONSTITS].[CONSTITUENT_LOCALID] = [CONSTITRANKS].[CONSTITUENT_LOCALID]' + char(13) +
        'where isnull([CONSTITRANKS].[RANK], 1) = 1;' + char(13);
    exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;

  end
  end
  else 
  begin 
  select @MATH = 
    case @AGGREGATETYPE
    when 0 then 'asc' --First gift

    when 1 then 'desc' --Last gift

    end

  --Full Refresh

  if @ASOF is null or @ASOF < (select [DATECHANGED] from dbo.[IDSETREGISTER] where [ID] = @GIFTSELECTION)
  begin
    set @SQL = 'with ';

    if @INCLUDE in (1, 2) --soft or both

    set @SQL = @SQL +
        '[GIFTLEVELIDS] ([ID]) as ' + char(13) +
        '( ' + char(13) +
        'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
        '), ' + char(13);

    set @SQL = @SQL +
    '[GIFTRANKS] ([CONSTITUENTID], [AMOUNT], [DATE], [DATEADDED], [GIFTSPLITID]) as ' + char(13) +
    '(' + char(13);

    if @INCLUDE in (0, 2) --hard only or both

      set @SQL = @SQL +
      '  select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID] ' + char(13) + 
      '  from dbo.[V_QUERY_RE7_GIFT] [GIFT]' + char(13) + 
      '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

    if @INCLUDE = 2
    set @SQL = @SQL + 'union' + char(13);

      if @INCLUDE in (1, 2) --soft only or both

        set @SQL = @SQL +
        '  select [SC].[CONSTITUENT_LOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID] ' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) + 
        '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[GIFTID]' + char(13) + 
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

      set @SQL = @SQL +
    '), ' + char(13) +
        '[CONSTITRANKS] ([CONSTITUENT_LOCALID], [RANK], [GIFTSPLITID]) as' + char(13) + 
        '(' + char(13) + 
        '  select [GIFT].[CONSTITUENTID], dense_rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[DATE] ' + @MATH + ', [GIFT].[DATEADDED] ' + @MATH + ',[GIFT].[GIFTSPLITID] ' + @MATH + ') [RANK], [GIFTSPLITID]' + char(13) + 
        '  from [GIFTRANKS] [GIFT]' + char(13) + 
        ')' + char(13) + 
        'select [CONSTITUENT].[LOCALID], isnull([CONSTITRANKS].[GIFTSPLITID],0)' + char(13) + 
        'from dbo.[V_QUERY_RE7_CONSTITUENT] [CONSTITUENT]' + char(13) + 
        'left join [CONSTITRANKS] on [CONSTITRANKS].[CONSTITUENT_LOCALID] = [CONSTITUENT].[LOCALID]' + char(13) + 
        'where isnull([CONSTITRANKS].[RANK], 1) = 1;' + char(13); 

    exec sp_executesql @SQL;
  end
  --Incremental Refresh

  else
  begin
    set @SQL = 'with ';
    if @INCLUDE in (1, 2) --soft or both

    set @SQL = @SQL +
        '[GIFTLEVELIDS] ([ID]) as ' + char(13) +
        '( ' + char(13) +
        'select distinct [GIFT].[GIFTID] from dbo.[V_QUERY_RE7_GIFT] [GIFT] inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13) +
        '), ' + char(13);

    --Find all affected constituents

    set @SQL = @SQL +
      '[CONSTITS] ([CONSTITUENT_LOCALID]) as' + char(13) +
    '(' + char(13);

    if @INCLUDE in (0,2) --hard only or both

      set @SQL = @SQL + 
        '  select isnull([VIEW].[CONSTITUENT_SYSTEMID],[RSD].[CONSTITUENT_LOCALID])' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFT] [VIEW]' + char(13) + 
        '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [VIEW].[GIFTSPLITID]' + char(13) + 
        '  full join [RE7_REVENUESPLIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_SYSTEMID]' + char(13) + 
        '  group by [VIEW].[CONSTITUENT_SYSTEMID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) + 
        '  having max([VIEW].[DATELASTCHANGED]) > @ASOF' + char(13) + 
        '  or max([DELETEDDATE]) > @ASOF' + char(13);

    if @INCLUDE = 2
    set @SQL = @SQL + 'union' + char(13);

    if @INCLUDE in (1,2)--soft only or both

      set @SQL = @SQL + 
        '    select isnull([VIEW].[CONSTITUENT_LOCALID],[RSD].[CONSTITUENT_LOCALID])' + char(13) +
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [VIEW]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [VIEW].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
        '  full join [RE7_SOFTCREDIT_DELETEDID] [RSD] on [RSD].[CONSTITUENT_LOCALID] = [VIEW].[CONSTITUENT_LOCALID]' + char(13) +
        '  group by [VIEW].[CONSTITUENT_LOCALID], [RSD].[CONSTITUENT_LOCALID] ' + char(13) +
        '  having (max([VIEW].[REVENUE_DATECHANGED]) > @ASOF) or (max([RSD].[DELETEDDATE]) > @ASOF)' + char(13);

    set @SQL = @SQL +
    '), ' + char(13) +
    '[GIFTRANKS] ([CONSTITUENTID], [AMOUNT], [DATE], [DATEADDED], [GIFTSPLITID]) as' + char(13) +
    '(' + char(13);

      if @INCLUDE in (0, 2) --hard only or both

      set @SQL = @SQL +
      '  select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID] ' + char(13) + 
      '  from [CONSTITS]' + char(13) + 
        '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [CONSTITS].[CONSTITUENT_LOCALID] = [GIFT].[CONSTITUENT_SYSTEMID]' + char(13) + 
        '  inner join dbo.' + @IDSET + ' [SELECTION] on [SELECTION].[ID] = [GIFT].[GIFTSPLITID]' + char(13);

    if @INCLUDE = 2
    set @SQL = @SQL + 'union' + char(13);

    if @INCLUDE in (1, 2) --soft only or both

        set @SQL = @SQL +
        '  select [SC].[CONSTITUENT_lOCALID], [SC].[AMOUNT], [GIFT].[DATE], [GIFT].[DATEADDED], [GIFT].[GIFTSPLITID] ' + char(13) + 
        '  from [CONSTITS]' + char(13) + 
            '  inner join dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC] on [CONSTITS].[CONSTITUENT_LOCALID] = [SC].[CONSTITUENT_LOCALID]' + char(13) + 
            '  inner join dbo.[V_QUERY_RE7_GIFT] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[GIFTID]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13);

    set @SQL = @SQL + 
    '), ' + char(13) +
    --Calculate new values with available data

      'CONSTITRANKS([CONSTITUENT_LOCALID], [RANK], [GIFTSPLITID]) as' + char(13) + 
      '(' + char(13) + 
      '  select [GIFT].[CONSTITUENTID], dense_rank() over(partition by [GIFT].[CONSTITUENTID] order by [GIFT].[DATE] ' + @MATH + ', [GIFT].[DATEADDED] ' + @MATH + ', [GIFT].[GIFTSPLITID] ' + @MATH + ') [RANK], [GIFT].[GIFTSPLITID]' + char(13) + 
      '  from [GIFTRANKS] [GIFT]' + char(13) + 
      ')' + char(13) + 
      'select [CONSTITS].[CONSTITUENT_LOCALID], isnull([CONSTITRANKS].[GIFTSPLITID],0)' + char(13) + 
      'from [CONSTITS] ' + char(13) + 
      'left join [CONSTITRANKS] on [CONSTITRANKS].[CONSTITUENT_LOCALID] = [CONSTITS].[CONSTITUENT_LOCALID]' + char(13) + 
      'where isnull([CONSTITRANKS].[RANK], 1) = 1;' + char(13);
    exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;
  end
  end

  return 0;