USP_SIMPLEDATALIST_CONSTITUENTHOUSEHOLDMEMBERS

CREATE procedure dbo.USP_SIMPLEDATALIST_CONSTITUENTHOUSEHOLDMEMBERS
(
    @CONSTITUENTID uniqueidentifier
)
as
    set nocount on;

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

    select distinct
        CONSTITUENT.ID as VALUE,
        NF.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
        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
        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