UFN_FUNDRAISINGGROUP_DONORRETAINEDNUMBER

create function dbo.UFN_FUNDRAISINGGROUP_DONORRETAINEDNUMBER
(
    @ID uniqueidentifier
)
returns int
with execute as caller
as begin
    return
    (
      select case when count(RC.ID)=0 then 0 else cast(COUNT(A.RID) as decimal)/cast(COUNT(RC.ID) as decimal) end
        from dbo.UFN_FAF_GETTEAMHIEARACHY(@ID) T
        inner join TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=T.teamID
        inner join dbo.EVENTEXTENSION ET ON ET.EVENTID=TE.EVENTID
        left join dbo.REVENUERECOGNITION RC ON RC.CONSTITUENTID=TE.TEAMCONSTITUENTID
        left join (
            select TE1.TEAMCONSTITUENTID
            , TE1.EventID
            ,RC1.ID RID 
            from dbo.UFN_FAF_GETTEAMHIEARACHY(@ID) T1
            inner join TEAMEXTENSION TE1 ON TE1.TEAMFUNDRAISINGTEAMID=T1.teamID
            inner join dbo.REVENUERECOGNITION RC1 ON RC1.CONSTITUENTID=TE1.TEAMCONSTITUENTID
        ) A ON A.TEAMCONSTITUENTID=TE.TEAMCONSTITUENTID and A.EVENTID=ET.PRIORYEAREVENTID
   )
end