UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS

            CREATE function dbo.UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS
            (
                @CONSTITUENTID uniqueidentifier,
                @COUNTRYID uniqueidentifier,
                @STATEID uniqueidentifier,
                @ADDRESSBLOCK nvarchar(150),
                @CITY nvarchar(50),
                @POSTCODE nvarchar(12),    
                @ADDRESSTYPECODEID uniqueidentifier
            ) returns @CONSTITUENTSWITHMATCHINGPHONE 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
                    @CONSTITUENTSWITHMATCHINGPHONE
                select
                    A.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.ADDRESS A
                inner join
                    HOUSEHOLDCONSTITUENTID on A.CONSTITUENTID = HOUSEHOLDCONSTITUENTID.ID
                left outer join
                    dbo.CONSTITUENT C on C.ID = A.CONSTITUENTID
                where
                    A.COUNTRYID = @COUNTRYID
                and
                    ( (A.STATEID = @STATEID) or (A.STATEID is null and @STATEID is null) )
                and
                    A.ADDRESSBLOCK = @ADDRESSBLOCK
                and
                    A.CITY = @CITY
                and
                    A.POSTCODE = @POSTCODE
                and 
                    ( (A.ADDRESSTYPECODEID = @ADDRESSTYPECODEID) or (A.ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null) );

                return;
            end