UFN_REGISTRANT_GROUPMEMBERLIST

CREATE function dbo.UFN_REGISTRANT_GROUPMEMBERLIST()
returns table
as
return 
(
    select REG.ID REGISTRANTID
    ,TE.TYPECODE 
    ,TFT.ID GROUPID
    ,TFT.NAME GROUPNAME
    ,count(AA.CONSTITUENTID) as MEMBERRETAINEDNUMBER
    ,(count(REG.CONSTITUENTID)-count(AA.CONSTITUENTID)) AS MEMBERNEWNUMBER
    from TEAMFUNDRAISINGTEAM TFT
    INNER JOIN TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
    INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID=TFT.ID
    INNER JOIN TEAMFUNDRAISER TFR ON TFR.ID=TFTM.TEAMFUNDRAISERID
    INNER JOIN REGISTRANT REG ON REG.CONSTITUENTID=TFR.CONSTITUENTID and REG.EVENTID = TE.EVENTID
    INNER JOIN dbo.EVENTEXTENSION EVE ON EVE.EVENTID=TE.EVENTID
    LEFT JOIN (
            select --distinct REG1.ID REGISTRANTID,

            TE1.EVENTID 
            ,TFR1.CONSTITUENTID
            ,TE1.TYPECODE 
            from TEAMFUNDRAISINGTEAM TFT1
            INNER JOIN TEAMEXTENSION TE1 ON TE1.TEAMFUNDRAISINGTEAMID=TFT1.ID
            INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM1 ON TFTM1.TEAMFUNDRAISINGTEAMID=TFT1.ID
            INNER JOIN TEAMFUNDRAISER TFR1 ON TFR1.ID=TFTM1.TEAMFUNDRAISERID
            --INNER JOIN REGISTRANT REG1 ON REG1.CONSTITUENTID=TFR1.CONSTITUENTID

    ) AA ON 
    REG.CONSTITUENTID = AA.CONSTITUENTID AND EVE.PRIORYEAREVENTID = AA.EVENTID AND AA.TYPECODE=TE.TYPECODE 
    GROUP BY REG.ID,TE.TYPECODE,TFT.ID,TFT.NAME 
)