UFN_FAF_CONSTITUENTS_BY_TEAMID

CREATE FUNCTION DBO.UFN_FAF_CONSTITUENTS_BY_TEAMID
(
 @TEAMID uniqueidentifier = NULL
,@EVENTID uniqueidentifier = NULL
)
RETURNS TABLE
AS
RETURN
(
 select TFT.ID TEAMID  
 ,TFT.NAME TEAMNAME  
 ,TE.TYPE TEAMTYPE  
 ,TE.TYPECODE TEAMTYPECODE  
 ,C.ID CONSTITUENTID  
 ,C.KEYNAME  CONSTITUENTNAME  
  ,RG.ID REGISTRANTID  
  ,(
        CASE TE.TYPECODE --now that there can be co-leaders or participants can be promoted, check for other leaders on the team


            WHEN 2 THEN --company

                CASE 
                    WHEN TCAPT.CONSTITUENTID is null THEN 'Individual' 
                    ELSE 'Company Leader'
                END
            ELSE  --for households or team

                CASE 
                    WHEN TCAPT.CONSTITUENTID is null THEN TE.TYPE + ' Member'
                    ELSE TE.TYPE + ' Leader'
                END
        END) as 'TEAMROLETYPE'
 from TEAMFUNDRAISINGTEAM TFT  
 INNER JOIN TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID  
 INNER JOIN EVENT EV ON EV.APPEALID=TFT.APPEALID   
 INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID=TFT.ID  
 INNER JOIN TEAMFUNDRAISER TF ON TF.ID=TFTM.TEAMFUNDRAISERID  
 INNER join REGISTRANT RG ON TF.CONSTITUENTID = RG.CONSTITUENTID AND RG.EVENTID = EV.ID  
 INNER JOIN CONSTITUENT  C ON C.ID=RG.CONSTITUENTID
 LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TCAPT on TCAPT.CONSTITUENTID = C.ID  
    WHERE EV.ID=@EVENTID
    AND TFT.ID=@TEAMID
)