UFN_FAF_GROUPCOUNT_BY_EVENTID

CREATE function dbo.UFN_FAF_GROUPCOUNT_BY_EVENTID(@EVENTID uniqueidentifier)  
 returns @GROUPCOUNT table     
(  
  maxhousehold int,  
  maxteammembers int,  
  maxcompanyteams int,  
  maxcompanymembers int  
)  
 AS  
  BEGIN  
declare @TEAMS table(ID int IDENTITY(1,1), TEAMID uniqueidentifier, TYPECODE tinyint);  
declare @i int = 1  
declare @TOTAL int  
declare @maxhousehold int = 0;  
declare @maxteammembers int = 0;  
declare @maxcompanyteams int = 0;  
declare @maxcompanymembers int = 0;  
declare @TEAMID uniqueidentifier  
declare @maxhouseholdtmp int = 0;  
declare @maxteammemberstmp int = 0;  
declare @maxcompanyteamstmp int = 0;  
declare @maxcompanymemberstmp int = 0;  
declare @teamtype tinyint  
declare @topTeamTypeCode smallint

INSERT INTO @TEAMS(TEAMID, TYPECODE)  
SELECT TEAMFUNDRAISINGTEAMID, TYPECODE from dbo.TEAMEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID   

SELECT @TOTAL = count(*) from @TEAMS  

WHILE @i <= @TOTAL  
BEGIN  

      SET @TEAMID = null;  
      SET @teamtype = null;  
      SELECT @TEAMID = TEAMID, @teamtype = TYPECODE FROM @TEAMS WHERE ID = @i;  

     IF @teamtype = 3  
     select @maxhouseholdtmp = count(*) from dbo.UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID(@TEAMID)  
     ELSE SET @maxhouseholdtmp = 0;  

     IF @teamtype = 1  
     select @maxteammemberstmp = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID)  
     ELSE SET @maxteammemberstmp = 0;  

     IF @teamtype = 2  
     BEGIN  
        select @maxcompanyteamstmp = count(*) from dbo.UFN_FAF_GETTEAMHIEARACHY(@TEAMID) where PARENTTEAMID IN   
                        (SELECT TEAMFUNDRAISINGTEAMID FROM dbo.TEAMEXTENSION where EVENTID=@EVENTID and TYPECODE = 2)  
     END  
     ELSE SET @maxcompanyteamstmp = 0;  

     IF @teamtype = 2  
     BEGIN  
     select @maxcompanymemberstmp = dbo.UFN_FAF_TEAMEMBERCOUNT_BY_COMPANYID(@TEAMID)     
     END  
     ELSE SET @maxcompanymemberstmp = 0;  

   IF @maxhouseholdtmp > @maxhousehold  
    SET @maxhousehold = @maxhouseholdtmp  

  IF @maxteammemberstmp > @maxteammembers  
    SET @maxteammembers = @maxteammemberstmp  

     IF @maxcompanyteamstmp > @maxcompanyteams  
    SET @maxcompanyteams = @maxcompanyteamstmp  

     IF @maxcompanymemberstmp > @maxcompanymembers  
   SET @maxcompanymembers = @maxcompanymemberstmp  

     SET @i = @i + 1;  

END  


 BEGIN  
   INSERT INTO @GROUPCOUNT(maxhousehold,maxteammembers,maxcompanyteams,maxcompanymembers)  
    VALUES(@maxhousehold,@maxteammembers,@maxcompanyteams,@maxcompanymembers)  
 END  
 RETURN;  
END