UFN_QUERY_GROUPMEMBERS_2

CREATE function dbo.UFN_QUERY_GROUPMEMBERS_2
(
    @GROUPID uniqueidentifier
)
returns table
as
return
(

    with CTE_CURRENTGROUPMEMBER as
    (
        select
            GROUPMEMBER.ID as GROUPMEMBERID,
            GROUPMEMBER.MEMBERID as MEMBERID,
            GROUPMEMBER.ISPRIMARY as ISPRIMARY,
            GROUPMEMBERDATERANGE.DATEFROM as STARTDATE,
            GROUPMEMBERDATERANGE.DATETO as ENDDATE,
            GROUPMEMBERDATERANGE.COMMENTS as COMMENTS,
            CONSTITUENT.FIRSTNAME as FIRSTNAME,
            CONSTITUENT.KEYNAME as KEYNAME
        from 
            dbo.GROUPMEMBER
            left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
            inner join dbo.CONSTITUENT on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
        where 
            GROUPMEMBER.GROUPID = @GROUPID 
            and
            (
                (
                    GROUPMEMBERDATERANGE.DATEFROM is null 
                    and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > getdate())
                ) 
                or
                (
                    GROUPMEMBERDATERANGE.DATETO is null 
                    and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= getdate())
                ) 
                or
                (
                    GROUPMEMBERDATERANGE.DATEFROM <= getdate() 
                    and GROUPMEMBERDATERANGE.DATETO > getdate()
                )
            )
    )
    select
        CURRENTGROUPMEMBER.GROUPMEMBERID as GROUPMEMBERID,
        CURRENTGROUPMEMBER.MEMBERID as CONSTITUENTID,
        case
            when CURRENTGROUPMEMBER.ISPRIMARY = 1 
                then 'CATALOG:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.primary_16-c.png'
            else 'RES:lv_spacer'
        end as ISPRIMARYIMAGE,
        CURRENTGROUPMEMBER.ISPRIMARY as ISPRIMARY,
        (
            rtrim(DISPLAYNAME.NAME + ' ' +
                coalesce(
                    replace(
                        stuff(
                            (select '' + STATUS 
                                from(
                                    select ',(Deceased)' STATUS
                                    from dbo.DECEASEDCONSTITUENT
                                    where DECEASEDCONSTITUENT.ID = CURRENTGROUPMEMBER.MEMBERID
                                    union all
                                    select ',(Inactive)' STATUS
                                    from dbo.CONSTITUENT
                                    where CONSTITUENT.ID = CURRENTGROUPMEMBER.MEMBERID
                                        and CONSTITUENT.ISINACTIVE=1
                                ) as SUBQ for xml path('')
                            )
                        ,1,1,'')
                    , '),(', ', ')
                ,'')
            )
        ) as NAME,
        CURRENTGROUPMEMBER.STARTDATE as STARTDATE,
        CURRENTGROUPMEMBER.ENDDATE as ENDDATE,
        case
            when CURRENTGROUPMEMBER.ISPRIMARY = 1 
                then ''
            else 
            (
                select 
                    dbo.UDA_BUILDLIST(distinct RELATIONSHIPTYPECODE.DESCRIPTION)
                from 
                    dbo.RELATIONSHIP
                    inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                where 
                    CURRENTGROUPMEMBER.MEMBERID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID 
                    and    RELATIONSHIP.RECIPROCALCONSTITUENTID = PRIMARYMEMBER.MEMBERID
            )
        end as RELATIONSHIPWITHPRIMARY,
        row_number() over (order by CURRENTGROUPMEMBER.ISPRIMARY desc, DISPLAYNAME.NAME) as SEQUENCE,
        CURRENTGROUPMEMBER.COMMENTS as COMMENTS,
        CURRENTGROUPMEMBER.FIRSTNAME,
        CURRENTGROUPMEMBER.KEYNAME
    from 
        CTE_CURRENTGROUPMEMBER as CURRENTGROUPMEMBER
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CURRENTGROUPMEMBER.MEMBERID) DISPLAYNAME
        outer apply (select MEMBERID from dbo.GROUPMEMBER where GROUPID = @GROUPID and ISPRIMARY = 1) PRIMARYMEMBER
)