USP_NUMBEROFTEAMMEMBERSTODATE

CREATE procedure dbo.USP_NUMBEROFTEAMMEMBERSTODATE
(
    @ASOF datetime 
)
as
    ;With [All] (TeamID, ParentID, MEMBERS)
  As 
  (        
      select TFT.ID, TFT.PARENTTEAMID, COUNT(TFTM.ID) MEMBERS 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    
  )

  select ParentID as ID, SUM(MEMBERS) as Value
  from [Final]
  group by ParentID