UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT

   CREATE FUNCTION dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT  
   (    
    @CONSTITUENTID uniqueidentifier,
    @DATE datetime,    
    @INCLUDEHOUSEHOLDPROCESSING bit    
   )    
   returns table    
   as    
   return    
    (    
     with HOUSEHOLD_CTE as (    
      select    
       CONSTITUENT.ID [CONSTITUENTID],    
       GROUPDATA.ID [HOUSEHOLDID]    
      from    
       dbo.CONSTITUENT    
      inner join    
       dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID    
      inner join    
       dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID    
      inner join    
       dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GROUPMEMBER.ID    
      where    
        GROUPDATA.GROUPTYPECODE = 0    
      and    
       ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    
        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))     
        or (GMDR.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE) and GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    
     )    
     select    
      C.ID [ID],    
      C.ID [CONSTITUENTID],    
      case    
       when GROUPDATA.GROUPTYPECODE = 0 then C.ID    
       else HOUSEHOLD_CTE.HOUSEHOLDID    
      end [HOUSEHOLDID],    
      0 [RETURNEDASHOUSEHOLDMEMBER],    
      C.ISORGANIZATION,    
      C.ISGROUP    
     from    
      dbo.CONSTITUENT C    
     left outer join    
      HOUSEHOLD_CTE on HOUSEHOLD_CTE.CONSTITUENTID = C.ID    
     left outer join    
      dbo.GROUPDATA on GROUPDATA.ID = C.ID    
     where C.ID = @CONSTITUENTID

    union  

     select    
      CONSTITUENT.ID,    
      CONSTITUENT.ID,    
      CONSTITUENT.ID,    
      1,    
      0,    
      1    
     from    
      dbo.CONSTITUENT    
     inner join    
      dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID    
     where 
     GROUPDATA.ID  = @CONSTITUENTID   
     and
      GROUPDATA.GROUPTYPECODE = 0    
     and    
      @INCLUDEHOUSEHOLDPROCESSING = 1    

    union  

     select    
      GD.ID,    
      GM.MEMBERID,    
      GD.ID,    
      1,    
      0, -- org    

      1 -- group         

     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
      @INCLUDEHOUSEHOLDPROCESSING = 1    
     and    
      GD.GROUPTYPECODE = 0    
     and    
      ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    
       or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))     
       or (GMDR.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE) and GMDR.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    

    )