USP_RE7SMARTFIELD_GIFTDATES

create procedure dbo.[USP_RE7SMARTFIELD_GIFTDATES]
( 
  @DATETYPE 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);
  declare @MATH nvarchar(50);

  set @IDSET = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME] (@GIFTSELECTION);
  select @MATH = 
    case @DATETYPE
    when 0 then 'min([DATES].[DATE])' --first gift date

    when 1 then 'max([DATES].[DATE])' --last gift date

    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)
      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 +
      '[DATES] ([CONSTITUENTID], [DATE]) as' + char(13) +
      '(' + char(13);

    if @INCLUDE in (0,2)
      set @SQL = @SQL +
        'select [GIFT].[CONSTITUENT_SYSTEMID], [GIFT].[DATE]' + 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

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

    if @INCLUDE in (1,2)
      set @SQL = @SQL +
        'select [SC].[CONSTITUENT_LOCALID], [GIFT].[DATE]' + char(13) + 
        '  from dbo.[V_QUERY_RE7_GIFTSOFTCREDIT] [SC]' + char(13) +
        '  inner join [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
        '  inner join dbo.[RE7_REVENUE] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[LOCALID]' + char(13);

    set @SQL = @SQL +
      '), ' + char(13) +
      '[CALC] ([ID], [VALUE]) as' + char(13) + 
      '(' + char(13) + 
      '  select [DATES].[CONSTITUENTID], '+ @MATH + char(13) + 
      '  from [DATES]' + char(13) + 
      '  group by [DATES].[CONSTITUENTID]' + char(13) + 
      ')' + char(13) + 
      'select [CONSTITUENT].[LOCALID], [CALC].[VALUE]' + char(13) + 
      'from dbo.[V_QUERY_RE7_CONSTITUENT] [CONSTITUENT]' + char(13) + 
      'left join [CALC] on [CALC].[ID] = [CONSTITUENT].[LOCALID];' + char(13);  

    exec sp_executesql @SQL;
  end
  --Incremental Refresh

  else
  begin
    --Find all affected constituents

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

    if @INCLUDE in (1,2)--soft only 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 +
      '[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--both

      set @SQL = @SQL +
        'union all' + 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) +
      '[DATES] ([CONSTITUENTID], [DATE]) as' + char(13) +
      '(' + char(13);

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

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

    if @INCLUDE = 2--both

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

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

      set @SQL = @SQL +
        ' select [SC].[CONSTITUENT_LOCALID], [GIFT].[DATE]' + 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 [GIFTLEVELIDS] on [SC].[REVENUE_LOCALID] = [GIFTLEVELIDS].[ID]' + char(13) +
        '  inner join dbo.[RE7_REVENUE] [GIFT] on [SC].[REVENUE_LOCALID] = [GIFT].[LOCALID]' + char(13);

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

      '[CALC] ([ID], [VALUE]) as' + char(13) + 
      '(' + char(13) + 
      ' select [DATES].[CONSTITUENTID], ' + @MATH + char(13) + 
      '  from [CONSTITS]' + char(13) + 
      '  inner join [DATES] on [CONSTITS].[CONSTITUENT_LOCALID] = [DATES].[CONSTITUENTID]' + char(13) + 
      '  group by [DATES].[CONSTITUENTID]' + char(13) + 
      ')' + char(13) + 
      --Return affected constituents with new values

      'select [CONSTITS].[CONSTITUENT_LOCALID], [CALC].[VALUE]' + char(13) + 
      'from [CONSTITS]' + char(13) + 
      'left join [CALC] on [CALC].[ID] = [CONSTITS].[CONSTITUENT_LOCALID];' + char(13);  

    exec sp_executesql @SQL, N'@ASOF datetime', @ASOF = @ASOF;
  end

  return 0;