USP_SIMPLEDATALIST_INTERACTIONPARTICIPANTCANDIDATE

                CREATE procedure dbo.USP_SIMPLEDATALIST_INTERACTIONPARTICIPANTCANDIDATE
                (
                    @CONSTITUENTID uniqueidentifier
                )
                as
                    set nocount on;

                    -- Create a table variable to hold the participants.  This is used rather than 

                    -- a union to prevent the same constituent from being returned multiple times.

                    declare @participants table
                    (
                        CONSTITUENTID uniqueidentifier,
                        DESCRIPTION nvarchar(100),
                        PRIMARYCONSTITUENT bit,
                        KEYNAME nvarchar(100),
                        FIRSTNAME nvarchar(100)
                    );

                    declare @CURRENTDATE date;
                    set @CURRENTDATE = getdate();

                    -- the record if it's a household, or the household an individual belongs to    

                    declare @HOUSEHOLDID uniqueidentifier; 
                    if exists(select 1 from dbo.CONSTITUENT where ID = @CONSTITUENTID and ISGROUP = 1)
                        set @HOUSEHOLDID = @CONSTITUENTID;
                    else
                        select
                            @HOUSEHOLDID = GM.GROUPID
                        from
                            dbo.GROUPMEMBER GM
                        left outer join
                            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                        left outer join
                            dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                        where
                            GM.MEMBERID = @CONSTITUENTID
                        and
                            GD.GROUPTYPECODE = 0
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE))) 
                            or (GMDR.DATEFROM <= dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE) and GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)));

                    insert into @participants (CONSTITUENTID, DESCRIPTION, PRIMARYCONSTITUENT, KEYNAME, FIRSTNAME)
                    select
                        C.ID,
                        case 
                            when GM.ISPRIMARY = 1 then substring(NF.NAME + ' - Member (Primary contact)', 0, 100)
                            else substring(NF.NAME + ' - Member', 0, 100)
                        end,
                        GM.ISPRIMARY,
                        C.KEYNAME,
                        C.FIRSTNAME
                    from
                        dbo.GROUPMEMBER GM
                    inner join
                        dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                    left join 
                        dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    where
                        GM.GROUPID = @HOUSEHOLDID
                    and
                        GM.MEMBERID <> @CONSTITUENTID
                    and 
                        ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE))) 
                        or (GMDR.DATEFROM <= dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE) and GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)));

                    -- relationships for all

                    insert into @participants (CONSTITUENTID, DESCRIPTION, PRIMARYCONSTITUENT, KEYNAME, FIRSTNAME)
                    select
                        C.ID,
                        substring((NF.NAME + ' - ' + (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=R.RECIPROCALTYPECODEID)), 0, 100),
                        ISPRIMARYCONTACT,
                        C.KEYNAME,
                        C.FIRSTNAME
                    from
                        dbo.RELATIONSHIP R
                    inner join
                        dbo.CONSTITUENT C on R.RECIPROCALCONSTITUENTID = C.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    where
                        R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID 
                    and
                        not exists (select 1 from @participants where CONSTITUENTID = C.ID)

                    select
                        CONSTITUENTID as 'VALUE',
                        DESCRIPTION as 'LABEL'
                    from
                        @participants
                    order by
                        PRIMARYCONSTITUENT desc,
                        KEYNAME,
                        FIRSTNAME