UFN_FAFGROUP_GETPARTICIPANTRETENTIONRATE

create function dbo.UFN_FAFGROUP_GETPARTICIPANTRETENTIONRATE()
returns table
as return (
  select
        A.ParentID as GROUPID, 
        A.EventID as EVENTID, 
        TX.TYPECODE, 
        A.ParentTeamConstituentID as CONSTITUENTID,
            count(pTF.ID) as TOTALPARTICIPANTSRETAINED,
        FGSI.TOTALPARTICIPANTS as TOTALPARTICIPANTSPREVIOUS,
        cast(convert(decimal(5,2),COUNT(pTF.ID)) / convert(decimal(5,2),FGSI.TOTALPARTICIPANTS) as decimal(5,2)) as [PARTICIPANTRETENTIONRATE]
  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.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = A.TeamID
  left join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
  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.TEAMFUNDRAISINGTEAMMEMBER pTFTM (nolock) on pTFTM.TEAMFUNDRAISINGTEAMID = A.TeamID
  left join dbo.TEAMFUNDRAISER pTF (nolock) on pTF.ID = TFTM.TEAMFUNDRAISERID and pTF.CONSTITUENTID = TF.CONSTITUENTID
  left join dbo.FAFGROUPSUMMARYINFORMATION FGSI (nolock) on FGSI.ID = A1.ParentID
  where A1.ParentID is not null 
  group by A.ParentID, A.EventID, FGSI.TOTALPARTICIPANTS, TX.TYPECODE, A.ParentTeamConstituentID
)