UFN_CONSTITUENT_MATCHINGHOUSEHOLDRECORDS

            CREATE function dbo.UFN_CONSTITUENT_MATCHINGHOUSEHOLDRECORDS
            (
                @CONSTITUENTID uniqueidentifier
            ) returns @CONSTITUENTSWITHMATCHINGEMAILADDRESS table(CONSTITUENTID uniqueidentifier) as
            begin
                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

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

                declare @HOUSEHOLDID uniqueidentifier; 
                if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 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 > @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));

                declare @PRIMARYMEMBERID uniqueidentifier;
                select
                    @PRIMARYMEMBERID = GM.MEMBERID
                from
                    dbo.GROUPMEMBER GM
                where
                    GM.GROUPID = @HOUSEHOLDID
                and
                    GM.ISPRIMARY = 1;

                with HOUSEHOLDCONSTITUENTID(ID) as
                (
                    select
                        GM.MEMBERID as ID
                    from
                        dbo.GROUPMEMBER GM
                    left outer join
                        dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where
                        GM.GROUPID = @HOUSEHOLDID
                    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
                        GM.MEMBERID <> @CONSTITUENTID
                    union all
                    select
                        @HOUSEHOLDID
                    where
                        @HOUSEHOLDID <> @CONSTITUENTID
                )
                insert into
                    @CONSTITUENTSWITHMATCHINGEMAILADDRESS
                select * from HOUSEHOLDCONSTITUENTID

                return;
            end