USP_SIMPLEDATALIST_HOUSEHOLDMEMBERSFORRECOGNITIONSETTINGSEDIT

                CREATE procedure dbo.USP_SIMPLEDATALIST_HOUSEHOLDMEMBERSFORRECOGNITIONSETTINGSEDIT
                (
                    @GROUPID uniqueidentifier,
                    @SPECIFICCONSTITUENTTOEXCLUDE uniqueidentifier = null
                )
                as
                set nocount on;

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

                select
                    GM.MEMBERID as VALUE,
                    (select C.NAME from dbo.CONSTITUENT C where C.ID=GM.MEMBERID) as LABEL
                from dbo.GROUPMEMBER GM
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where
                    GM.GROUPID = @GROUPID
                    and
                    (@SPECIFICCONSTITUENTTOEXCLUDE is null or GM.MEMBERID <> @SPECIFICCONSTITUENTTOEXCLUDE)
                    -- 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 > @CURRENTDATEEARLIESTTIME))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                    or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                order by LABEL