UFN_SPONSORSHIPCANCELLATIONREPORT_CHECKTENURE

CREATE function dbo.UFN_SPONSORSHIPCANCELLATIONREPORT_CHECKTENURE(
@SPONSORSHIPCANCELLATIONREPORTTENURECODEID uniqueidentifier,
@SPONSORSHIPID uniqueidentifier,
@ENDDATE datetime
)
returns bit
with execute as caller
as begin

  declare @DATEFROM date
  declare @TENURE int 

    declare @CONSTITUENTID uniqueidentifier;
    select @CONSTITUENTID = R.CONSTITUENTID from dbo.SPONSORSHIP S
    inner join dbo.REVENUESPLIT RS on RS.ID = S.REVENUESPLITID
    inner join dbo.REVENUE R on R.ID = RS.REVENUEID
    where S.ID = @SPONSORSHIPID

    declare @DATETO datetime
    select @DATEFROM = max(DATEFROM) from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @CONSTITUENTID;

    select @DATETO=DATETO from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @CONSTITUENTID and DATEFROM = @DATEFROM

    if @DATEFROM is null
      return 0

    if @DATETO is null
      set @ENDDATE = getdate();

  select 
    @TENURE = SEQUENCE 
  from dbo.SPONSORSHIPCANCELLATIONREPORTTENURECODE where ID = @SPONSORSHIPCANCELLATIONREPORTTENURECODEID

  if @TENURE = 0
  begin
    if @DATEFROM > DATEADD(month,-6,@ENDDATE)
      return 1
  end

  if @TENURE = 1
  begin
    if (@DATEFROM <= DATEADD(month,-6,@ENDDATE)) and (@DATEFROM > DATEADD(month,-12,@ENDDATE))
      return 1
  end

  if @TENURE = 2
  begin
    if (@DATEFROM <= DATEADD(year,-1,@ENDDATE)) and (@DATEFROM > DATEADD(year,-2,@ENDDATE))
      return 1
  end

  if @TENURE = 3
  begin
    if (@DATEFROM <= DATEADD(year,-2,@ENDDATE)) and (@DATEFROM > DATEADD(year,-3,@ENDDATE))
      return 1
  end

  if @TENURE = 4
  begin
    if (@DATEFROM <= DATEADD(year,-3,@ENDDATE)) and (@DATEFROM > DATEADD(year,-5,@ENDDATE))
      return 1
  end

  if @TENURE = 5
  begin
    if (@DATEFROM <= DATEADD(year,-5,@ENDDATE))
      return 1
  end

    return 0
end