USP_SIMPLEDATALIST_HOUSEHOLDMEMBERS

                CREATE procedure dbo.USP_SIMPLEDATALIST_HOUSEHOLDMEMBERS
                (
                    @CONSTITID uniqueidentifier
                )
                as
                    set nocount on

                    declare @GROUPID uniqueidentifier;
                    declare @ISGROUP bit;
                    set @ISGROUP = 0;

                    select 
                        @GROUPID = GROUPID 
                    from 
                        dbo.GROUPMEMBER
                    inner join dbo.CONSTITUENT C on C.ID = GROUPMEMBER.GROUPID and C.ISINACTIVE = 0 -- Check if group is active

                    inner join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GROUPMEMBER.ID -- Check if constituent is a group member

                    inner join
                        dbo.GROUPDATA
                    on
                        GROUPTYPECODE = 0  and GROUPID = GROUPDATA.ID
                    where
                        MEMBERID = @CONSTITID

                    if @GROUPID is null
                        begin
                        select 
                            @GROUPID = GROUPID,
                            @ISGROUP = 1
                        from 
                            dbo.GROUPMEMBER
                        inner join dbo.CONSTITUENT C on C.ID = GROUPMEMBER.GROUPID and C.ISINACTIVE = 0 -- Check if group is active

                        inner join
                            dbo.GROUPDATA
                        on
                            GROUPTYPECODE = 0  and GROUPID = GROUPDATA.ID
                        where
                            GROUPID = @CONSTITID
                        end

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    if @GROUPID is null
                        select @CONSTITID as VALUE,
                               NF.NAME as LABEL
                        from dbo.UFN_CONSTITUENT_DISPLAYNAME(@CONSTITID) NF
                    else
                        select
                            GM.MEMBERID as VALUE,
                            NF.NAME as LABEL
                        from dbo.GROUPMEMBER GM
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = GM.MEMBERID and CONSTITUENT.ISINACTIVE = 0
                        inner join dbo.CONSTITUENT GC on GC.ID = GM.GROUPID and GC.ISINACTIVE = 0 -- Check if group is active

                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) NF
                        left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                        where
                            GM.GROUPID = @GROUPID
                            -- the GMDR.DATETO is set to the earliest time on that day, so use a strictly greater than in checks using current day

                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                            or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                            and not exists (select 1 from dbo.DECEASEDCONSTITUENT where ID = GM.MEMBERID)
                        union all
                        select @CONSTITID as VALUE,
                               NF.NAME as LABEL
                        from dbo.UFN_CONSTITUENT_DISPLAYNAME(@CONSTITID) NF
                        where @ISGROUP = 1
                        order by NF.NAME