UFN_FAF_HOUSEHOLDMEMBERLIST

This function takes the registrantID and return a list of members from the same household that registrant belongs to

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_FAF_HOUSEHOLDMEMBERLIST
(
@CONSTITUENTID uniqueidentifier = NULL
,@EVENTID uniqueidentifier = NULL
)

   returns table  
   as  
   return  
   (  
    select  
     FNTEAM.TEAMNAME,  
     FNTEAM.TEAMID,
     C.keyname, 
     C.name,
     C.ID 
    from  
     dbo.TEAMFUNDRAISINGTEAMMEMBER TM
     INNER JOIN dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
        ON TM.TEAMFUNDRAISINGTEAMID = FNTEAM.TEAMID
        AND FNTEAM.TEAMTYPECODE = 3 --household

     INNER JOIN dbo.TEAMFUNDRAISER TF
        ON TM.TEAMFUNDRAISERID = TF.ID
     INNER JOIN dbo.CONSTITUENT C
        ON TF.CONSTITUENTID = C.ID
    left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --exclude household leaders

        on FNTEAM.TEAMID = TC.TEAMFUNDRAISINGTEAMID
        and C.ID = TC.CONSTITUENTID
    where TC.TEAMFUNDRAISINGTEAMID is null
)