UFN_FAFNFGCAMPAIGN_GETSUMMARY

CREATE function dbo.UFN_FAFNFGCAMPAIGN_GETSUMMARY()
returns table
as return 
  select
      C.ID, 
      C.NAME, 
      C.CONTACTID,
      C.NFGTYPECODEID, 
      C.CAMPAIGNID,
      sum(isnull(IG.TOTALFUNDRAISE,0)) TOTALRAISED, 
      sum(isnull(IG.TOTALDONORCOUNT,0)) AS TOTALDONOR, 
      sum(isnull(IG.TOTALGIFTCOUNT,0)) AS TOTALGIFT,
      sum(isnull(TOTALGROUPS, -1) +1) TOTALGROUP,  -- +1 to include self for NFG

      sum(isnull(TOTALCOMMUNICATIONSENT, 0)) AS TOTALCOMMUNICATIONSENT, 
      sum(isnull(TOTALPARTICIPANTS,0)) TOTALPARTICIPANT,
      isnull(RET.TOTALGROUPRETAINED, 0) TOTALGROUPRETAINED,
      isnull(RET.GROUPRETENTIONRATE, 0) GROUPRETENTIONRATE,
      isnull(RET.TOTALPARTICIPANTRETAINED, 0) TOTALPARTICIPANTRETAINED,
      isnull(RET.PARTICIPANTRETENTIONRATE, 0) PARTICIPANTRETENTIONRATE
    from dbo.FAFPROGRAM FC (nolock)
    join dbo.FAFNFGCAMPAIGN C (nolock) on C.CAMPAIGNID = FC.ID and FC.ISACTIVE = 1
    join dbo.FAFNFGCAMPAIGNLEVEL CL (nolock) on C.ID = CL.NFGCAMPAIGNID
    left join dbo.TEAMEXTENSION TX (nolock) on TX.NFGCAMPAIGNLEVELID = CL.ID
    left join dbo.FAFGROUPSUMMARYINFORMATION IG (nolock) on IG.ID = TX.TEAMFUNDRAISINGTEAMID 
    left join dbo.UFN_FAFNFGCAMPAIGN_GETRETENTION() RET on RET.ID = C.ID 
    group by C.ID, C.NAME, C.CONTACTID, C.NFGTYPECODEID, C.CAMPAIGNID,
      RET.TOTALGROUPRETAINED,
      RET.GROUPRETENTIONRATE,
      RET.TOTALPARTICIPANTRETAINED,
      RET.PARTICIPANTRETENTIONRATE