UFN_GROUPMEMBER_HOUSEHOLDCOUNT

            CREATE function dbo.UFN_GROUPMEMBER_HOUSEHOLDCOUNT
      (
          @CONSTITUENTID uniqueidentifier
      )
      returns int
      as
      begin
          declare @CURRENTDATE date;
          set @CURRENTDATE = getdate();

          declare @HOUSEHOLDCOUNT int;

          select 
              @HOUSEHOLDCOUNT = count(GM.MEMBERID)
          from dbo.GROUPMEMBER GM
          inner join dbo.GROUPDATA GD on GD.ID = GM.GROUPID
          left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
          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));

          return @HOUSEHOLDCOUNT;
      end