UFN_NUMBERGROUPMEMBERS

CREATE function [dbo].[UFN_NUMBERGROUPMEMBERS]
(
    @TEAMFUNDRAISINGTEAMID as uniqueidentifier
)
returns TABLE
as
return

With [All] (TeamID, ParentID, MEMBERS)
  As 
  (        
      select TFT.ID, TFT.PARENTTEAMID, COUNT(TFTM.ID) MEMBERS --TFT.name, 

      from TEAMFUNDRAISINGTEAMMEMBER TFTM
      join TEAMFUNDRAISINGTEAM TFT on TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
        join TEAMEXTENSION TE ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID  -- add this join here to make sure we only calculate FAF teams

      group by TFT.ID, TFT.PARENTTEAMID 
  ),
  [Final] 
  As
  (
      select TeamID as ParentID, TeamID, MEMBERS 
      from [All] 

      Union All

      select F.ParentID, A.TeamID, A.MEMBERS
      from [Final] F 
          join [All] A on F.TeamID = A.ParentID    
  ),
  [Final2] 
as
  (select F.ParentID, F.TeamID, TE.TYPECODE, F.MEMBERS, 'TOTALMEMBERS'= F.MEMBERS
  from [Final]  F
  JOIN TEAMEXTENSION TE ON  TE.TEAMFUNDRAISINGTEAMID= F.TeamID
 )

  select ParentID as ID, SUM(TOTALMEMBERS) as Value
  from [Final2] 
  where ParentID= @TEAMFUNDRAISINGTEAMID
  group by ParentID