UFN_FAFGROUP_GETDONORGIFTCOUNTANDAMOUNTRAISED

CREATE function dbo.UFN_FAFGROUP_GETDONORGIFTCOUNTANDAMOUNTRAISED()
returns table
as 
    return 
    With [MembersAndGroups]
    as (
        select 1 as ISGROUP, ParentID, TeamID, ConstituentID, EventID from dbo.UFN_FAFGROUP_GETALLGROUPS()
        Union

        select 0 as ISGROUP, f.ParentID, f.TeamID, TF.CONSTITUENTID, f.EventID 
        from dbo.UFN_FAFGROUP_GETALLGROUPS() F
        join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = F.TeamID
        join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
        group by f.EventID, f.ParentID, f.TeamID, TF.CONSTITUENTID
    )
    select F.EventID, F.ParentID as GROUPID, sum(isnull(T.DONORCOUNT,0)) DONORCOUNT, sum(isnull(T.GIFTCOUNT,0)) GIFTCOUNT, sum(isnull(T.AMOUNTRAISED, 0)) RAISEDTOTAL
    from [MembersAndGroups] F
      left join (
             select
             fr.EVENTID,
            CONSTITUENTID,
            AMOUNTRAISED = RECEIVEDAMOUNT
                + CASE WHEN opt.ISREGISTRATIONREVENUE = 1 then REGAMOUNT else 0 end
                + CASE WHEN opt.ISUNPAIDPLEDGES = 1 then UNPAIDPLEDGEAMOUNT else 0 end
                + CASE WHEN opt.ISUNPAIDRECURRINGGIFTS = 1 then UNPAIDRECURRINGAMOUNT else 0 end
                + CASE WHEN opt.ISPENDINGMATCHINGGIFTS = 1 then MATCHINGGIFTCLAIMAMOUNT else 0 end
                + CASE WHEN opt.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then UNCONFIRMEDOFFLINEAMOUNT else 0 end,
            DONORCOUNT,
            GIFTCOUNT = GIFTCOUNT_DONATION
                + CASE WHEN opt.ISUNPAIDPLEDGES = 1 then GIFTCOUNT_UNPAIDPLEDGES_ON else GIFTCOUNT_UNPAIDPLEDGES_OFF end
                + CASE WHEN opt.ISUNPAIDRECURRINGGIFTS = 1 then GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
                + CASE WHEN opt.ISPENDINGMATCHINGGIFTS = 1 then GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
                + CASE WHEN opt.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end
            from dbo.FAFRAISEDTOTAL fr (nolock)
            inner join dbo.FAFEVENTDONATIONOPTIONSCONFIG opt (nolock) on opt.EVENTID = fr.EVENTID    
        ) T on T.CONSTITUENTID = F.CONSTITUENTID and T.EVENTID = F.EventID
        group by F.EventID, F.ParentID