UFN_FAFADDRESSBOOK_ALLGROUPMEMBERS_LIST

CREATE FUNCTION [dbo].[UFN_FAFADDRESSBOOK_ALLGROUPMEMBERS_LIST]
(
    @EVENTID            AS uniqueidentifier, 
    @CLIENTUSERSID    AS INT
)
RETURNS TABLE
AS
RETURN
    WITH 
    -- Client user linked constituent id

    [CLIENTUSERCONSITUENTID]
    AS(
        SELECT    C.ID AS CONSTITUENTID, C.DATEADDED
        FROM    dbo.ClientUsers cu  (NOLOCK)
        INNER    JOIN dbo.BackOfficeSystemUsers bosu (NOLOCK)
        ON        cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0  
        INNER    JOIN dbo.BackOfficeSystemPeople bosp (NOLOCK)
        ON        bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0  
        INNER    JOIN dbo.CONSTITUENT C (NOLOCK)
        ON        bosp.BackofficeRecordID = C.SEQUENCEID    
        WHERE  cu.ID = @CLIENTUSERSID
    )
    ,[CURRENTEVENTAGMEMBERS]
    AS
    (
        SELECT    
             TF.CONSTITUENTID, TM.TEAMFUNDRAISINGTEAMID AS TEAMID, TM.DATEADDED, TFTC.CONSTITUENTID AS TEAMCAPTAINCONSTITUENTID, TE.TYPECODE, 
       E.ID, E.NAME, E.STARTDATE, T.NAME TNAME, T.GOAL TGOAL
        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      
        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
    )

  ,[PREVIOUSEVENTAGMEMBERS]
    AS
    (
        SELECT    
             TF.CONSTITUENTID, TM.TEAMFUNDRAISINGTEAMID AS TEAMID, TM.DATEADDED, TFTC.CONSTITUENTID AS TEAMCAPTAINCONSTITUENTID, TE.TYPECODE, 
       E.ID, E.NAME, E.STARTDATE, T.NAME TNAME, T.GOAL TGOAL
        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     
        JOIN    [dbo].[EVENT] E               (NOLOCK)      ON    TF.APPEALID = E.APPEALID
        JOIN    [dbo].[EVENTEXTENSION] EE     (NOLOCK)      ON    EE.PRIORYEAREVENTID    = E.ID
        LEFT JOIN [dbo].[TEAMFUNDRAISINGTEAMCAPTAIN] TFTC (NOLOCK)  ON TFTC.TEAMFUNDRAISINGTEAMID=T.ID
        WHERE    EE.EVENTID = @EVENTID
    )

        -- Client user team id

    ,[CLIENTUSERTEAM]
    AS
    (
        SELECT    TEAMID
        FROM    [CURRENTEVENTAGMEMBERS] T
        JOIN    [CLIENTUSERCONSITUENTID] C
        ON        T.CONSTITUENTID = C.CONSTITUENTID
    )
    -- FAF Address book contact related list

    ,[FAFADDRESSBOOKCONTACTS]
    AS
    (
        SELECT   A.ID AS CONTACTID, A.FIRSTNAME, A.LASTNAME, A.CONSTITUENTID 
            , CASE WHEN CE.CONSTITUENTID IS NOT NULL THEN 'C'                    END AS [GROUPSTATUS]
                    , CASE WHEN CE.CONSTITUENTID IS NOT NULL THEN CE.DATEADDED              END AS [DATEADDED]
            , CASE WHEN CE.CONSTITUENTID=CE.TEAMCAPTAINCONSTITUENTID THEN 'Y'    END AS [ISGROUPCAPTAIN]
            , CASE WHEN CE.TEAMCAPTAINCONSTITUENTID  IS NOT NULL THEN 'C'        END AS [GROUPCAPTAINSTATUS]
            , CASE WHEN CE.TYPECODE IS NOT NULL THEN CE.TYPECODE                END AS [TYPECODE]
            , CASE WHEN CE.ID IS NOT NULL THEN CE.ID                          END AS [EVENTID]
            , CASE WHEN CE.NAME IS NOT NULL THEN CE.NAME                      END AS [EVENTNAME]
            , CASE WHEN CE.STARTDATE IS NOT NULL THEN CE.STARTDATE            END AS [EVENTSTARTDATE]    
            , CASE WHEN CE.TEAMID IS NOT NULL THEN CE.TEAMID                  END AS [TEAMID]
            , CASE WHEN CE.TNAME IS NOT NULL THEN CE.TNAME                    END AS [TEAMNAME]
            , CASE WHEN CE.TGOAL IS NOT NULL THEN CE.TGOAL                    END AS [TEAMGOAL]
            , CE.TEAMCAPTAINCONSTITUENTID 

        FROM    [dbo].[ADDRESSBOOKFAF] A (NOLOCK)
        LEFT    JOIN  [CURRENTEVENTAGMEMBERS] CE      ON    A.CONSTITUENTID = CE.CONSTITUENTID
        JOIN          [CLIENTUSERTEAM] CT                ON    CT.TEAMID = CE.TEAMID
        WHERE       A.CLIENTUSERSID=@CLIENTUSERSID

    UNION

        SELECT   A.ID AS CONTACTID, A.FIRSTNAME, A.LASTNAME, A.CONSTITUENTID 
            , CASE WHEN PE.CONSTITUENTID IS NOT NULL THEN 'P'                    END AS [GROUPSTATUS]
                    , CASE WHEN PE.CONSTITUENTID IS NOT NULL THEN PE.DATEADDED              END AS [DATEADDED]
            , CASE WHEN PE.CONSTITUENTID=PE.TEAMCAPTAINCONSTITUENTID THEN 'Y'    END AS [ISGROUPCAPTAIN]
            , CASE WHEN PE.TEAMCAPTAINCONSTITUENTID  IS NOT NULL THEN 'P'        END AS [GROUPCAPTAINSTATUS]
            , CASE WHEN PE.TYPECODE IS NOT NULL THEN PE.TYPECODE                END AS [TYPECODE]
            , CASE WHEN PE.ID IS NOT NULL THEN PE.ID                          END AS [EVENTID]
            , CASE WHEN PE.NAME IS NOT NULL THEN PE.NAME                      END AS [EVENTNAME]
            , CASE WHEN PE.STARTDATE IS NOT NULL THEN PE.STARTDATE            END AS [EVENTSTARTDATE]    
            , CASE WHEN PE.TEAMID IS NOT NULL THEN PE.TEAMID                  END AS [TEAMID]
            , CASE WHEN PE.TNAME IS NOT NULL THEN PE.TNAME                    END AS [TEAMNAME]
            , CASE WHEN PE.TGOAL IS NOT NULL THEN PE.TGOAL                    END AS [TEAMGOAL]
            , PE.TEAMCAPTAINCONSTITUENTID 

        FROM    [dbo].[ADDRESSBOOKFAF] A (NOLOCK)
        LEFT    JOIN  [PREVIOUSEVENTAGMEMBERS] PE      ON    A.CONSTITUENTID = PE.CONSTITUENTID         
        JOIN          [CLIENTUSERTEAM] CT                ON    CT.TEAMID = PE.TEAMID
        WHERE       A.CLIENTUSERSID=@CLIENTUSERSID

    )
    SELECT * FROM  [FAFADDRESSBOOKCONTACTS];