UFN_FAFADDRESSBOOK_PARTICIPANTS_LIST

CREATE FUNCTION [dbo].[UFN_FAFADDRESSBOOK_PARTICIPANTS_LIST]  
(  
  @EVENTID  AS uniqueidentifier  
 ,@CLIENTUSERID AS INT  
)  
RETURNS TABLE  
AS  
RETURN  
 WITH   
 [CURRENTEVENTPARTICIPANTS]  
 AS  
 (  
    SELECT   Reg.CONSTITUENTID, Reg.DATEADDED  
    FROM   dbo.EVENT E                       (NOLOCK)   
    JOIN   dbo.REGISTRANT Reg                 (NOLOCK) ON E.ID = Reg.EVENTID AND Reg.EVENTID = @EVENTID
    JOIN   dbo.CONSTITUENT C                  (NOLOCK) ON Reg.CONSTITUENTID = C.ID AND ISORGANIZATION=0  
    LEFT JOIN dbo.TEAMFUNDRAISER TFR          (NOLOCK) ON C.ID = TFR.CONSTITUENTID   
    LEFT JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID   
    LEFT JOIN dbo.TEAMEXTENSION TE        (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID AND TE.EVENTID = E.ID
    WHERE     E.ID = @EVENTID 
    AND     TFTM.TEAMFUNDRAISINGTEAMID IS NULL OR TYPECODE= 2  
 )  
 ,[PREVIOUSEVENTPARTICIPANTS]  
 AS  
 (  
    SELECT   Reg.CONSTITUENTID, Reg.DATEADDED  
    FROM     dbo.EVENT E                       (NOLOCK)   
    JOIN     dbo.EVENTEXTENSION EE               (NOLOCK) ON E.ID = EE.PRIORYEAREVENTID  
    JOIN   dbo.REGISTRANT Reg                 (NOLOCK) ON E.ID = Reg.EVENTID AND Reg.EVENTID = EE.PRIORYEAREVENTID
    JOIN   dbo.CONSTITUENT C                  (NOLOCK) ON Reg.CONSTITUENTID = C.ID AND ISORGANIZATION=0  
    LEFT JOIN dbo.TEAMFUNDRAISER TFR          (NOLOCK) ON C.ID = TFR.CONSTITUENTID   
    LEFT JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (NOLOCK) ON TFR.ID = TFTM.TEAMFUNDRAISERID   
    LEFT JOIN dbo.TEAMEXTENSION TE        (NOLOCK) ON TFTM.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID AND TE.EVENTID = EE.EVENTID
    WHERE    EE.EVENTID =  @EVENTID 
    AND     TFTM.TEAMFUNDRAISINGTEAMID IS NULL OR TYPECODE= 2  
 )  
 ,[FAFAddressBook]  
 AS  
 (  
  SELECT   
    [ID] AS [CONTACTID]  
   , [CLIENTUSERSID]  
   , [FIRSTNAME]  
   , [LASTNAME]  
   , [CONSTITUENTID]  
  FROM [dbo].[ADDRESSBOOKFAF] (nolock)  
  WHERE [CLIENTUSERSID] in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID)) 
 )  
 ,participants  

 AS  
 (  
  SELECT   DISTINCT  
              A.CONTACTID  
            , A.FIRSTNAME  
            , A.LASTNAME  
             ,A.CONSTITUENTID  
            , CASE   
              WHEN CD.CONSTITUENTID IS NOT NULL THEN 'A'  
              WHEN PD.CONSTITUENTID IS NOT NULL THEN 'P'  
            END AS STATUS  
            , CASE  
         WHEN CD.CONSTITUENTID IS NOT NULL THEN CD.DATEADDED  
         WHEN PD.CONSTITUENTID IS NOT NULL THEN PD.DATEADDED  
        END AS [DATEADDED]  

  FROM       [FAFAddressBook] A  
  LEFT JOIN   CURRENTEVENTPARTICIPANTS CD     ON A.CONSTITUENTID = CD.CONSTITUENTID  
  LEFT JOIN   PREVIOUSEVENTPARTICIPANTS PD    ON A.CONSTITUENTID = PD.CONSTITUENTID  
  WHERE       A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID)) 
 )  
 SELECT * FROM participants WHERE [STATUS] IS NOT NULL;