UFN_GROUPMEMBER_HOUSEHOLDCOUNTVALID

            CREATE function dbo.UFN_GROUPMEMBER_HOUSEHOLDCOUNTVALID
            (
                @MEMBERID uniqueidentifier,
                @GROUPID uniqueidentifier
            )
            returns bit
            as
            begin
                -- Always return true for non-household groups since a member can be a member of multiple of those

                declare @ISHOUSEHOLD bit;
                select
                    @ISHOUSEHOLD = case when GROUPTYPECODE = 0 then 1 else 0 end
                from dbo.GROUPDATA
                where ID = @GROUPID;

                if @ISHOUSEHOLD = 0
                    return 1;

                -- Check if the constituent is an active member of other households already

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                -- If were updating a group member record that is in the past

                -- Then it doesn't matter if there is another household.

                if exists ( select 
                                1
                            from
                                dbo.GROUPMEMBER GM
                            inner join
                                dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where
                                GM.MEMBERID = @MEMBERID and
                                GM.GROUPID = @GROUPID and
                                GMDR.DATETO is not null and
                                GMDR.DATETO <= @CURRENTDATE 
                                ) 
                    return 1;

                if exists (    select
                                1
                            from dbo.GROUPMEMBER GM
                            inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                            inner join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where
                                GM.MEMBERID = @MEMBERID and
                                GM.GROUPID <> @GROUPID 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)))
                    return 0;

                return 1;
            end