UFN_EMAILADDRESS_MATCHINGHOUSEHOLDRECORDS

            CREATE function dbo.UFN_EMAILADDRESS_MATCHINGHOUSEHOLDRECORDS
            (
                @CONSTITUENTID uniqueidentifier,
                @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                @EMAILADDRESSTYPECODEID uniqueidentifier
            ) returns @CONSTITUENTSWITHMATCHINGEMAILADDRESS table(CONSTITUENTID uniqueidentifier, NAME nvarchar(154), RELATIONSHIPTOPRIMARY nvarchar(300)) 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
                    E.CONSTITUENTID,
                    C.NAME,
                    (select dbo.UDA_BUILDLIST(distinct RTC.DESCRIPTION)
                    from dbo.RELATIONSHIP R
                    left outer join dbo.RELATIONSHIPTYPECODE RTC
                    on R.RELATIONSHIPTYPECODEID = RTC.ID
                    where C.ID = R.RELATIONSHIPCONSTITUENTID
                    and R.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID
                    ) RELATIONSHIPTOPRIMARY
                from
                    dbo.EMAILADDRESS E
                inner join
                    HOUSEHOLDCONSTITUENTID on E.CONSTITUENTID = HOUSEHOLDCONSTITUENTID.ID
                left outer join
                    dbo.CONSTITUENT C on C.ID = E.CONSTITUENTID
                where
                    E.EMAILADDRESS = @EMAILADDRESS
                and 
                    (E.EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID or (E.EMAILADDRESSTYPECODEID is null and @EMAILADDRESSTYPECODEID is null));

                return;
            end