UFN_ADDRESSPROCESS_GETCONSTITUENTS

            CREATE function dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS
            (
                @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

                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 GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left outer join
                        dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                    where
                        @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)))

                )