UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT_BULK

create function dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT_BULK(    
    @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 on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID    
        where GROUPDATA.GROUPTYPECODE = 0    
            and (
                (GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    
                or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))     
                or (GROUPMEMBERDATERANGE.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE) and GROUPMEMBERDATERANGE.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  

    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.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 on GROUPMEMBERDATERANGE.GROUPMEMBERID = GM.ID    
        left outer join dbo.GROUPDATA GD on GD.ID = GM.GROUPID    
    where @INCLUDEHOUSEHOLDPROCESSING = 1    
        and GD.GROUPTYPECODE = 0    
        and (
            (GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))    
            or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE)))     
            or (GROUPMEMBERDATERANGE.DATEFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE) and GROUPMEMBERDATERANGE.DATETO > dbo.UFN_DATE_GETEARLIESTTIME(@DATE))
        )    

)