USP_DATALIST_WEALTHINFORMATIONDASHBOARDGROUPRELATIONSHIPS

                create procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDGROUPRELATIONSHIPS
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1
                )
                as
                    set nocount on;

                    if @ISVISIBLE = 1
                    begin
                        select
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENT.ISGROUP = 1
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, NF.NAME;                            
                    end
                    else
                    begin
                        select top 1
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISSPOUSE
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENT.ISGROUP = 1
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, CONSTITUENT.NAME;                            
                    end