UFN_PARTICIPANT_DONOR_RETENTION

create function dbo.UFN_PARTICIPANT_DONOR_RETENTION
(
    @CONSTITUENTID uniqueidentifier,
    @EVENTID uniqueidentifier
)
returns @ret table
(
    RetainedDonorCount int,
    RetentionRate float
)
as
begin
    DECLARE @PREVIOUSEVENTID uniqueidentifier
    DECLARE @TEAMID uniqueidentifier
    DECLARE @PREVIOUSTEAMID uniqueidentifier
    DECLARE @TEAMCONSTITUENTID uniqueidentifier
    DECLARE @PREVIOUSTEAMCONSTITUENTID uniqueidentifier
    DECLARE @RETAINEDDONORCOUNT int = 0
    DECLARE @PRIORDONORCOUNT int = 0
    DECLARE @ISTEAMLEADER bit
    DECLARE @ISPREVIOUSTEAMLEADER bit

    SELECT @PREVIOUSEVENTID = PRIORYEAREVENTID from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID;  

    IF @PREVIOUSEVENTID IS NOT NULL  
    BEGIN  
        SELECT @TEAMID = TEAMID from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID);  
        SELECT @TEAMCONSTITUENTID = TEAMCONSTITUENTID from TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @TEAMID  
        IF EXISTS(SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN WHERE CONSTITUENTID = @CONSTITUENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID)  
            SET @ISTEAMLEADER = 1; 

        SELECT @PREVIOUSTEAMID = TEAMID from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @PREVIOUSEVENTID);  
        SELECT @PREVIOUSTEAMCONSTITUENTID = TEAMCONSTITUENTID from TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @PREVIOUSTEAMID;  
        IF EXISTS(SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN WHERE CONSTITUENTID = @CONSTITUENTID AND TEAMFUNDRAISINGTEAMID = @PREVIOUSTEAMID)  
            SET @ISPREVIOUSTEAMLEADER = 1  

        DECLARE @PREVDONORS as table([CONSTITUENTID]    uniqueidentifier)
        DECLARE @CURRDONORS as table([CONSTITUENTID]    uniqueidentifier)

        insert @PREVDONORS 
        select CONSTITUENTID from [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@PREVIOUSEVENTID, @CONSTITUENTID) 
        union 
        select CONSTITUENTID FROM [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@PREVIOUSEVENTID, @PREVIOUSTEAMCONSTITUENTID)
        where @PREVIOUSTEAMCONSTITUENTID IS NOT NULL AND ISNULL(@ISPREVIOUSTEAMLEADER, 0) = 1

        insert @CURRDONORS 
        select CONSTITUENTID from [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@EVENTID, @CONSTITUENTID) 
        union 
        select CONSTITUENTID FROM [dbo].[UFN_FAFEVENT_PARTICIPANTDONORS](@EVENTID, @TEAMCONSTITUENTID)
        where @TEAMCONSTITUENTID IS NOT NULL AND ISNULL(@ISTEAMLEADER, 0) = 1

        select @PRIORDONORCOUNT = COUNT(*) from @PREVDONORS  

        select @RETAINEDDONORCOUNT = COUNT(*)
        from @CURRDONORS C
        inner join @PREVDONORS P on P.CONSTITUENTID = C.CONSTITUENTID    
    END

    insert @ret (RetainedDonorCount, RetentionRate)
    values (@RETAINEDDONORCOUNT,
            case when @PRIORDONORCOUNT > 0 then cast(@RETAINEDDONORCOUNT as float)/cast(@PRIORDONORCOUNT as float) else 0 end)

    return
end