USP_DATALIST_RESEARCHGROUPMEMBERPROSPECTSUMMARY

CREATE procedure dbo.USP_DATALIST_RESEARCHGROUPMEMBERPROSPECTSUMMARY
(
    @RESEARCHGROUPID uniqueidentifier
)
as
    set nocount on;

    select 
        CONSTITUENT.ID,
        BUSINESS.NAME as PRIMARYBUSINESS,
        RELATIONSHIPJOBINFO.JOBTITLE POSITION,
        CONSTITUENT.NICKNAME,
        CONSTITUENT.GENDER,
        CONSTITUENT.BIRTHDATE,
        NF_SPOUSE.NAME as SPOUSENAME
    from 
        dbo.RESEARCHGROUPMEMBER
        left join dbo.CONSTITUENT on RESEARCHGROUPMEMBER.CONSTITUENTID = CONSTITUENT.ID
        left join dbo.RELATIONSHIP SPOUSERELATIONSHIP on SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and SPOUSERELATIONSHIP.ISSPOUSE = 1
        left join dbo.CONSTITUENT SPOUSE on SPOUSE.ID = SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID
        left join dbo.RELATIONSHIP BUSINESSRELATIONSHIP on BUSINESSRELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and BUSINESSRELATIONSHIP.ISPRIMARYBUSINESS = 1
        left join dbo.RELATIONSHIPSET BUSINESSRELATIONSHIPSET on BUSINESSRELATIONSHIP.RELATIONSHIPSETID = BUSINESSRELATIONSHIPSET.ID
        left join dbo.RELATIONSHIPJOBINFO on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = BUSINESSRELATIONSHIPSET.ID
        left join dbo.CONSTITUENT BUSINESS on BUSINESS.ID = BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
    where 
        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @RESEARCHGROUPID