UFN_MKTSMARTFIELD_SINGLEGIFT

Returns the loyalty score for a constituent based on giving counts by year.

Return

Return Type
nvarchar(50)

Parameters

Parameter	Parameter Type	Mode
@YEAR1	int	IN
@YEAR2	int	IN
@YEAR3	int	IN
@YEAR4	int	IN
@YEAR5	int	IN
@YEAR6	int	IN
@YEAR7	int	IN
@YEAR8	int	IN
@YEAR9	int	IN
@YEAR10	int	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSMARTFIELD_SINGLEGIFT]
(
  @YEAR1 integer,
  @YEAR2 integer,
  @YEAR3 integer,
  @YEAR4 integer,
  @YEAR5 integer,
  @YEAR6 integer,
  @YEAR7 integer,
  @YEAR8 integer,
  @YEAR9 integer,
  @YEAR10 integer
)
returns nvarchar(50)
as begin
  declare @VALUE nvarchar(50);

  set @VALUE =
    case when (@YEAR1 + @YEAR2 + @YEAR3 + @YEAR4 + @YEAR5 + @YEAR6 + @YEAR7 + @YEAR8 + @YEAR9 + @YEAR10) = 0 then 'No gifts'
           when (@YEAR1 = 1 and @YEAR2 = 1) then
           case when @YEAR3 = 1 then
             case when @YEAR4 = 1 then
               case when @YEAR5 = 1 then
                 case when @YEAR6 = 1 then
                 'Other' else '5 years' end
               else '4 years' end
             else '3 years' end
           else '2 years' end
         else 'Other' end;

  return @VALUE;
end;