UFN_RELATIONSHIPMAP_GETRELATIONSHIPS

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@TOPN	int	IN
Definition

Copy


            CREATE function [dbo].[UFN_RELATIONSHIPMAP_GETRELATIONSHIPS](
                @CONSTITUENTID uniqueidentifier,
        @CURRENTDATE datetime,
                @TOPN integer
            )
            returns table
            as
            return
            (
        with RELATIONSHIPSET_CTE as
        (
          select
            RELATIONSHIP.ID,
            RELATIONSHIP.RECIPROCALCONSTITUENTID,
            RELATIONSHIP.ISSPOUSE,
            CONSTITUENT.ISGROUP,
            CONSTITUENT.ISORGANIZATION
          from dbo.RELATIONSHIP
          inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
          where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
              and (@CURRENTDATE is null
                or RELATIONSHIP.STARTDATE is null
                or RELATIONSHIP.STARTDATE <= @CURRENTDATE)
              and (@CURRENTDATE is null
                or RELATIONSHIP.ENDDATE is null
                or RELATIONSHIP.ENDDATE >= @CURRENTDATE)
        )
        select 
          ID, 
          RECIPROCALCONSTITUENTID
        from RELATIONSHIPSET_CTE
        where 
          RECIPROCALCONSTITUENTID in  
            (
             select top (@TOPN)
                RECIPROCALCONSTITUENTID
              from RELATIONSHIPSET_CTE
              group by   
                RECIPROCALCONSTITUENTID,
                ISGROUP,
                ISORGANIZATION
              order by ROW_NUMBER() over (order by  max(case when ISSPOUSE = 1 then 1 else 0 end) desc, ISGROUP, ISORGANIZATION)
            ) 
    )