UFN_MKTSMARTFIELD_LOYALTY_2

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
@older	int	IN
Definition

Copy


CREATE function dbo.UFN_MKTSMARTFIELD_LOYALTY_2
(
  @year1 int,
  @year2 int,
  @year3 int,
  @year4 int,
  @year5 int,
  @year6 int,
  @year7 int,
  @year8 int,
  @year9 int,
  @year10 int,
  @older int = 0
)
returns nvarchar(50)
as begin
  declare @ret nvarchar(50);

  set @ret =
    case  when (@year1>0 and (@year2+@year3+@year4+@year5+@year6+@year7+@year8+@year9+@year10+@older)=0) then 'New'
      -- multis

      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6>0 and @year7>0 and @year8>0 and @year9>0 and @year10>0) then 'Multi (10+ years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6>0 and @year7>0 and @year8>0 and @year9>0 and @year10=0) then 'Multi (9 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6>0 and @year7>0 and @year8>0 and @year9=0) then 'Multi (8 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6>0 and @year7>0 and @year8=0) then 'Multi (7 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6>0 and @year7=0) then 'Multi (6 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5>0 and @year6=0) then 'Multi (5 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4>0 and @year5=0) then 'Multi (4 years)'
      when (@year1>0 and @year2>0 and @year3>0 and @year4=0) then 'Multi (3 years)'
      when (@year1>0 and @year2>0 and @year3=0) then 'Multi (2 years)'

      -- reactivated 

      when (@year1>0 and @year2=0 and (@year3+@year4+@year5+@year6+@year7+@year8+@year9+@year10+@older)>0) then 'Reactivated'

      -- lapsed

      when (@year1=0 and @year2>0 and (@year3+@year4+@year5+@year6+@year7+@year8+@year9+@year10)=0) then 'Lapsed (1yr-New)'
      when (@year1=0 and @year2>0 and (@year3+@year4+@year5+@year6+@year7+@year8+@year9+@year10)>0) then 'Lapsed (1yr)'
      when (@year1=0 and @year2=0 and @year3>0) then 'Lapsed (2yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4>0) then 'Lapsed (3yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5>0) then 'Lapsed (4yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6>0) then 'Lapsed (5yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7>0) then 'Lapsed (6yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7=0 and @year8>0) then 'Lapsed (7yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7=0 and @year8=0 and @year9>0) then 'Lapsed (8yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7=0 and @year8=0 and @year9=0 and @year10>0) then 'Lapsed (9 yr)'
      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7=0 and @year8=0 and @year9=0 and @year10=0 and @older>0) then 'Lapsed (10+ yr)'

      -- non- donors

      when (@year1=0 and @year2=0 and @year3=0 and @year4=0 and @year5=0 and @year6=0 and @year7=0 and @year8=0 and @year9=0 and @year10=0 and @older=0) then 'Non-donor'
    else 'N/A' end;

    return @ret;
end;