USP_SIMPLEDATALIST_CONSTITUENTHOUSEHOLDMEMBERSWITHRELATION

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

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

        select distinct
            CONSTITUENT.ID as VALUE,
            (case when RTC.DESCRIPTION is not null then NF.NAME + ' (' + RTC.DESCRIPTION + ')' else NF.NAME end) 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
            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
            left join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = HOUSEHOLDMEMBER.ID and R.RECIPROCALCONSTITUENTID = @CONSTITUENTID
            left join dbo.RELATIONSHIPTYPECODE RTC on R.RELATIONSHIPTYPECODEID = RTC.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where HOUSEHOLD.ID in (select HOUSEHOLDID from dbo.CONSTITUENTHOUSEHOLD where CONSTITUENTHOUSEHOLD.ID = @CONSTITUENTID)
            and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
            and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIESTTIME)
            and HOUSEHOLDMEMBER.ID <> @CONSTITUENTID
            and DECEASEDCONSTITUENT.ID is null