USP_SIMPLEDATALIST_BATCHCONSTITUENTHOUSEHOLDMEMBERSWITHRELATION

                create procedure dbo.USP_SIMPLEDATALIST_BATCHCONSTITUENTHOUSEHOLDMEMBERSWITHRELATION
                (
                    @CONSTITUENTID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @CURRENTDATEEARLIESTTIME date = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    select distinct
                        CONSTITUENTS.VALUE,
                        (case when RTC.DESCRIPTION is not null then NF.NAME + ' (' + RTC.DESCRIPTION + ')' else NF.NAME end) as LABEL,
                        CONSTITUENTS.DESCRIPTION
                    from (
                        -----

                        -- Existing constituents

                        -----


                        -- Household members

                        select
                            CONSTITUENT.ID as VALUE,
                            CONSTITUENT.NAME as LABEL,
                            CONSTITUENT.LOOKUPID as DESCRIPTION
                        from dbo.CONSTITUENT
                            inner join dbo.CONSTITUENTHOUSEHOLD HOUSEHOLDMEMBER on CONSTITUENT.ID = HOUSEHOLDMEMBER.ID
                            inner join dbo.CONSTITUENTHOUSEHOLD HOUSEHOLD on HOUSEHOLDMEMBER.HOUSEHOLDID = HOUSEHOLD.ID
                            left join dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                        where
                            HOUSEHOLD.ID in (select HOUSEHOLDID from dbo.CONSTITUENTHOUSEHOLD where CONSTITUENTHOUSEHOLD.ID = @CONSTITUENTID)
                            and HOUSEHOLDMEMBER.ID <> @CONSTITUENTID
                            and DECEASEDCONSTITUENT.ID is null

                        union all

                        -- Group members

                        select
                            CONSTITUENT.ID as VALUE,
                            CONSTITUENT.NAME as LABEL,
                            CONSTITUENT.LOOKUPID as DESCRIPTION
                        from dbo.CONSTITUENT
                            inner join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
                            inner join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                            left join dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                        where
                            GROUPMEMBER.GROUPID in (select GROUPID from dbo.GROUPMEMBER where MEMBERID = @CONSTITUENTID)
                            and(GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
                            and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIESTTIME)
                            and GROUPMEMBER.ID <> @CONSTITUENTID
                            and DECEASEDCONSTITUENT.ID is null

                        union all

                        -- Members of group (if provided constituent is a group)

                        select
                            CONSTITUENT.ID as VALUE,
                            CONSTITUENT.NAME as LABEL,
                            CONSTITUENT.LOOKUPID as DESCRIPTION
                        from dbo.CONSTITUENT
                            inner join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
                            inner join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                            left join dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                        where
                            GROUPID = @CONSTITUENTID
                            and(GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
                            and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIESTTIME)
                            and DECEASEDCONSTITUENT.ID is null

                        union all

                        -----

                        -- Batch constituents

                        -----


                        -- Group members

                        select
                            BATCHREVENUECONSTITUENT.ID as VALUE,
                            BATCHREVENUECONSTITUENT.NAME as LABEL,
                            null as DESCRIPTION
                        from dbo.BATCHREVENUECONSTITUENT
                            inner join dbo.BATCHREVENUECONSTITUENTGROUPMEMBER on BATCHREVENUECONSTITUENT.ID = BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
                        where
                            BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID <> @CONSTITUENTID
                            and GROUPID = (select top 1 GROUPID from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER where MEMBERID = @CONSTITUENTID)

                        union all

                        -- Members of group (if provided constituent is a group)

                        select
                            BATCHREVENUECONSTITUENT.ID as VALUE,
                            BATCHREVENUECONSTITUENT.NAME as LABEL,
                            null as DESCRIPTION
                        from dbo.BATCHREVENUECONSTITUENT
                            inner join dbo.BATCHREVENUECONSTITUENTGROUPMEMBER on BATCHREVENUECONSTITUENT.ID = BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
                        where
                            BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID <> @CONSTITUENTID
                            and GROUPID = @CONSTITUENTID
                        ) as CONSTITUENTS
                    left join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = CONSTITUENTS.VALUE and R.RECIPROCALCONSTITUENTID = @CONSTITUENTID
                    left join dbo.RELATIONSHIPTYPECODE RTC on R.RELATIONSHIPTYPECODEID = RTC.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTS.VALUE) NF
                    where CONSTITUENTS.VALUE <> @CONSTITUENTID;