UFN_FUNDRAISINGGROUP_MEMBERSNUMBER

CREATE function dbo.UFN_FUNDRAISINGGROUP_MEMBERSNUMBER(
)
returns TABLE
as
return

WITH 
[Tmp1]
as
(
    select TFT.ID GroupID,
    TFT.PARENTTEAMID ParGroupID,
    TE.TEAMCONSTITUENTID GConstituentID,
    TF.CONSTITUENTID,
    TE.EVENTID CEventID,
    CEvent.PRIORYEAREVENTID ParEventID
    from dbo.TEAMFUNDRAISINGTEAM TFT
    join dbo.TEAMEXTENSION TE ON  TE.TEAMFUNDRAISINGTEAMID = TFT.ID 
    join dbo.EVENTEXTENSION CEvent on CEvent.EVENTID = TE.EVENTID
    join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM  on TFTM.TEAMFUNDRAISINGTEAMID =  TFT.ID
    join dbo.TEAMFUNDRAISER TF ON TF.ID = TFTM.TEAMFUNDRAISERID
)
,[Tmp2]
as
(
    select CYear.GroupID, 
    CYear.ParGroupID,
    CYear.CEventID AS EventID,
    1 CMemberTag,
    (case when PYear.CONSTITUENTID is not null then 1 else 0 end) PMemberTag
    from [Tmp1] CYear
    left join [Tmp1] PYear on PYear.CEventID = CYear.ParEventID --and Pyear.GConstituentID = CYear.GConstituentID 

    and Pyear.CONSTITUENTID = Cyear.CONSTITUENTID 
)
,[Tmp3]
as
(
    select GroupID,
    ParGroupID,
    SUM(CmemberTag) MEMBERNUMBER,
    SUM(PMemberTag) MEMBERNUMBERRETAINED
    from [Tmp2]
    group by GroupID, ParGroupID
)
,[Tmp4]
as
(
    select GroupID AS ParGroupID, GroupID, MEMBERNUMBER, MEMBERNUMBERRETAINED
    from [Tmp3]

    union all

    select F.ParGroupID, A.GroupID, A.MEMBERNUMBER, A.MEMBERNUMBERRETAINED
      from [Tmp4] F 
          join [Tmp3] A on F.GroupID = A.ParGroupID    

)
,[Tmp5]
as
(
    select ParGroupID GroupID,
    SUM(MEMBERNUMBER) as MemberNumber,
    SUM(MEMBERNUMBERRETAINED) as MemberNumberRetained
    from [Tmp4]
    Group by ParGroupID
)
select GroupID,
MEMBERNUMBER,
MEMBERNUMBERRETAINED,
MEMBERNUMBER - MEMBERNUMBERRETAINED as MEMBERNUMBERNEW,
CASE WHEN MEMBERNUMBER=0 THEN 0 ELSE MEMBERNUMBERRETAINED/MEMBERNUMBER END AS PARTMEMBERNUMBERRETAINED
from [Tmp5]