UFN_FAFNFGCAMPAIGN_GETRETENTION

CREATE function dbo.UFN_FAFNFGCAMPAIGN_GETRETENTION()
returns table
as return
  select NFG.ID
        ,NFGRETAINED.CNT_PREVIOUSGROUP, NFGRETAINED.TOTALGROUPRETAINED, NFGRETAINED.GROUPRETENTIONRATE
            ,NFGPARTRETAINED.CNT_PREVIOUSPARTICIPANT, NFGPARTRETAINED.TOTALPARTICIPANTRETAINED, NFGPARTRETAINED.PARTICIPANTRETENTIONRATE
  from dbo.FAFNFGCAMPAIGN NFG (nolock) 
  left join (
          select FC.ID,
              count(PREVIOUS.TEAMCONSTITUENTID) as TOTALGROUPRETAINED, -- total local groups return to this NFG

              NFGS.TOTALGROUP as CNT_PREVIOUSGROUP,  -- total local groups this NFG has in previous campaign

              case NFGS.TOTALGROUP 
                  when 0 then 0 
                  else cast(convert(decimal(5,2),count(PREVIOUS.TEAMCONSTITUENTID)+sum(FSGI.TOTALGROUPSRETAINED)) / convert(decimal(5,2),NFGS.TOTALGROUP) as decimal(5,2)) end as [GROUPRETENTIONRATE]
           from dbo.FAFPROGRAM EC (nolock)
           join dbo.FAFNFGCAMPAIGN FC (nolock) on EC.ID = FC.CAMPAIGNID and EC.ISACTIVE = 1
           join dbo.FAFNFGCAMPAIGNLEVEL FCL (nolock) on FCL.NFGCAMPAIGNID = FC.ID 
           join dbo.TEAMEXTENSION TX (nolock) on TX.NFGCAMPAIGNLEVELID = FCL.ID 
           join dbo.EVENTEXTENSION EX (nolock) on EX.EVENTID = TX.EVENTID
           left join dbo.FAFGROUPSUMMARYINFORMATION FSGI (nolock) on FSGI.ID = TX.TEAMFUNDRAISINGTEAMID -- get total child group retained for each group link to NFG

           left join (
                select 
                PTX.TEAMCONSTITUENTID, FCL.NFGCAMPAIGNID, FC.GROUPCONSTITUENTID, EX.FAFPROGRAMID as EVENTCAMPAIGNID
                from dbo.FAFNFGCAMPAIGN FC (nolock)
                join dbo.FAFNFGCAMPAIGNLEVEL FCL (nolock) on FCL.NFGCAMPAIGNID = FC.ID 
                join dbo.TEAMEXTENSION PTX (nolock) on PTX.NFGCAMPAIGNLEVELID = FCL.ID 
                join dbo.EVENTEXTENSION EX (nolock) on EX.EVENTID = PTX.EVENTID 
           ) PREVIOUS on TX.TEAMCONSTITUENTID = [PREVIOUS].TEAMCONSTITUENTID 
                            and FC.GROUPCONSTITUENTID = [PREVIOUS].GROUPCONSTITUENTID
                            and EC.PREVIOUSCAMPAIGNID = [PREVIOUS].EVENTCAMPAIGNID
           left join dbo.FAFNFGLEVELSUMMARY NFGS (nolock) on NFGS.NFGID =[PREVIOUS].NFGCAMPAIGNID and NFGS.ISROOT = 1
           where PREVIOUS.TEAMCONSTITUENTID is not null
           group by FC.ID, FC.name, NFGS.TOTALGROUP
        ) NFGRETAINED on NFGRETAINED.ID = NFG.ID 
  left join (    
      select FC.ID,
        count(PREVIOUS.CONSTITUENTID) AS TOTALPARTICIPANTRETAINED,
            max(NFGS.TOTALPARTICIPANT) AS CNT_PREVIOUSPARTICIPANT,
        case NFGS.TOTALPARTICIPANT
          when 0 then 0 
          else cast(convert(decimal(5,2),count(PREVIOUS.CONSTITUENTID)) / convert(decimal(5,2),NFGS.TOTALPARTICIPANT) as decimal(5,2)) end as [PARTICIPANTRETENTIONRATE]
      from dbo.FAFPROGRAM EC (nolock)
      join dbo.FAFNFGCAMPAIGN FC (nolock) on EC.ID = FC.CAMPAIGNID and EC.ISACTIVE = 1
      join dbo.FAFNFGCAMPAIGNLEVEL FCL (nolock) on FCL.NFGCAMPAIGNID = FC.ID 
      join dbo.TEAMEXTENSION TX (nolock) on TX.NFGCAMPAIGNLEVELID = FCL.ID 
      join dbo.EVENTEXTENSION EX (nolock) on EX.EVENTID = TX.EVENTID 
      join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
      join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
      left join (
          select FC.ID, FC.GROUPCONSTITUENTID, FC.CAMPAIGNID,TF.CONSTITUENTID 
          from dbo.FAFNFGCAMPAIGN FC (nolock) 
          join dbo.FAFNFGCAMPAIGNLEVEL FCL (nolock) on FCL.NFGCAMPAIGNID = FC.ID 
          join dbo.TEAMEXTENSION TX (nolock) on TX.NFGCAMPAIGNLEVELID = FCL.ID 
          join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
          join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID 
      ) PREVIOUS on    FC.GROUPCONSTITUENTID = [PREVIOUS].GROUPCONSTITUENTID
                      and EC.PREVIOUSCAMPAIGNID = [PREVIOUS].CAMPAIGNID
                      and TF.CONSTITUENTID = PREVIOUS.CONSTITUENTID
      left join dbo.FAFNFGLEVELSUMMARY NFGS  (nolock) on NFGS.NFGID =[PREVIOUS].ID and NFGS.ISROOT = 1
      where PREVIOUS.CONSTITUENTID is not null
      group by FC.ID, FC.name, NFGS.TOTALPARTICIPANT
  ) NFGPARTRETAINED on NFG.ID = NFGPARTRETAINED.ID 
  where GROUPRETENTIONRATE is not null or PARTICIPANTRETENTIONRATE is not null