UFN_FAF_GETTEAMINFO

This table function return team ID and name for a given constituentid and event id

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN
@TYPECODE	int	IN

Definition

Copy



CREATE function dbo.UFN_FAF_GETTEAMINFO
(
@CONSTITUENTID uniqueidentifier
,@EVENTID uniqueidentifier
,@TYPECODE integer = 1
)
returns table
AS 
return
(
  SELECT 
   T.Name
   ,TF.CONSTITUENTID  
   ,TM.TEAMFUNDRAISINGTEAMID AS TEAMID
   ,TM.DATEADDED
   ,(
    SELECT TOP 1 TFTC.CONSTITUENTID
    FROM [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK) WHERE TFTC.TEAMFUNDRAISINGTEAMID=T.ID    
    ORDER BY TFTC.DATEADDED
   ) AS TEAMCAPTAINCONSTITUENTID  
   ,TE.TEAMCONSTITUENTID
  FROM [dbo].[TEAMFUNDRAISINGTEAMMEMBER] TM (NOLOCK)  
  JOIN [dbo].[TEAMFUNDRAISER] TF     (NOLOCK)               ON TM.TEAMFUNDRAISERID = TF.ID  
  JOIN [dbo].[TEAMFUNDRAISINGTEAM] T (NOLOCK)               ON TM.TEAMFUNDRAISINGTEAMID = T.ID  AND  TF.APPEALID = T.APPEALID  
  JOIN  [dbo].[TEAMEXTENSION] TE    (NOLOCK)                ON  T.ID= TE.TEAMFUNDRAISINGTEAMID AND TE.TYPECODE = @TYPECODE  
  JOIN [dbo].[EVENT] E               (NOLOCK)               ON  TF.APPEALID = E.APPEALID  
  --LEFT JOIN [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK)  ON  TFTC.TEAMFUNDRAISINGTEAMID=T.ID  

  WHERE E.ID = @EVENTID
  AND TF.CONSTITUENTID = @CONSTITUENTID
)