USP_DATALIST_RELATIONSHIPSBYMARRIAGE

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

  declare @SPOUSEID uniqueidentifier;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  select
    @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID
  from
    dbo.CONSTITUENT CONSTIT
  left join dbo.RELATIONSHIP
    on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT.ID and RELATIONSHIP.ISSPOUSE = 1
  left join dbo.CONSTITUENT SPOUSE
    on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
  where 
    CONSTIT.ID = @CONSTITUENTID

  select 
    CONSTITUENTID,
    NF.NAME + ' (' + (
      select 
        dbo.UDA_BUILDLIST(NF_SPOUSE.NAME + '''s ' + RELATIONSHIPTYPECODE.DESCRIPTION)
      from dbo.RELATIONSHIP
      inner join dbo.RELATIONSHIPTYPECODE 
        on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
      left join dbo.RELATIONSHIPCONFIGURATION
        on RELATIONSHIPTYPECODE.ID = RELATIONSHIPCONFIGURATION.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIPCONSTITUENTID) NF_SPOUSE
      where 
        RELATIONSHIP.RECIPROCALCONSTITUENTID = RELS.CONSTITUENTID and
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID in (@CONSTITUENTID, @SPOUSEID) and
        (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= @CURRENTDATE) and
        (RELATIONSHIPCONFIGURATION.ID is null or RELATIONSHIPCONFIGURATION.APPLIESTOINDIVIDUALS = 1) and
        RELATIONSHIP.ID not in (select RELATIONSHIPID from dbo.RELATIONSHIPBYMARRIAGE) and
        not exists --exclude deceased

        (
          select 
            ID 
          from dbo.DECEASEDCONSTITUENT 
          where DECEASEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and 
          not RELATIONSHIP.RECIPROCALCONSTITUENTID in (@CONSTITUENTID, @SPOUSEID) --but include deceased spouses

        )
    ) + ')' AS NAME,
    (select count(ID) from dbo.UFN_RELATIONSHIPSBYMARRIAGE(@CONSTITUENTID, CONSTITUENTID)) NUM_RELATIONSHIPS,
    convert(nvarchar(36),@CONSTITUENTID) + convert(nvarchar(36), CONSTITUENTID) PARENTIDCHILDID,
    (select ID from dbo.RELATIONSHIP where RECIPROCALCONSTITUENTID = @CONSTITUENTID and ISSPOUSE = 1) MARRIAGERELATIONSHIPID --needed for add action

  from     
  (
    select distinct
      RECIPROCALCONSTITUENTID as CONSTITUENTID
    from dbo.RELATIONSHIP
    left join dbo.RELATIONSHIPCONFIGURATION
      on RELATIONSHIP.RECIPROCALTYPECODEID = RELATIONSHIPCONFIGURATION.ID
    where 
      RELATIONSHIPCONSTITUENTID in (@CONSTITUENTID,@SPOUSEID) and
      (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= @CURRENTDATE) and
      (RELATIONSHIPCONFIGURATION.ID is null or RELATIONSHIPCONFIGURATION.APPLIESTOINDIVIDUALS = 1) and
      RELATIONSHIP.ID not in (select RELATIONSHIPID from dbo.RELATIONSHIPBYMARRIAGE) and
      not exists --exclude deceased

      (
        select ID 
        from dbo.DECEASEDCONSTITUENT 
        where DECEASEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and 
        not RELATIONSHIP.RECIPROCALCONSTITUENTID in (@CONSTITUENTID, @SPOUSEID) --but include deceased spouses

      )
  ) RELS 
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTID) NF
  order by NAME;