UFN_FAFGROUP_GETGROUPRETENTIONRATE

CREATE function dbo.UFN_FAFGROUP_GETGROUPRETENTIONRATE()
returns table
as return (
  select 
        A.ParentID as GROUPID,  
            A.EventID as EVENTID,
            A.ParentTeamConstituentID as CONSTITUENTID ,
            TX.TYPECODE,
            COUNT(A1.teamID) as TOTALGROUPSRETAINED, 
            FGSI.TOTALGROUPS as TOTALGROUPSPREVIOUS,
          cast(convert(decimal(5,2),COUNT(A1.teamID)) / convert(decimal(5,2),FGSI.TOTALGROUPS) as decimal(5,2)) as [GROUPRETENTIONRATE]
  from dbo.UFN_FAFGROUP_GETALLGROUPS () A
  join EVENTEXTENSION EX (nolock) on A.EventID = EX.EVENTID 
  join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = A.ParentID
  left join dbo.UFN_FAFGROUP_GETALLGROUPS() A1 on A1.ConstituentID = A.ConstituentID and A1.ParentTeamConstituentID = A.ParentTeamConstituentID and A1.EventID = EX.PRIORYEAREVENTID
  left join dbo.FAFGROUPSUMMARYINFORMATION FGSI (nolock) on FGSI.ID = A1.ParentID
  where A1.ParentID is not null and A1.ParentID <> A1.TeamID
  group by A.ParentID, A.EventID, FGSI.TOTALGROUPS, TX.TYPECODE, A.ParentTeamConstituentID
)