USP_DATALIST_GROUPMEMBERWITHROLECURRENT

                CREATE procedure dbo.USP_DATALIST_GROUPMEMBERWITHROLECURRENT
                (
                    @GROUPID uniqueidentifier 
                )
                as
                    set nocount on;
                    declare @CURRENTDATEEARLIESTTIME date;
                    set @CURRENTDATEEARLIESTTIME = getdate();

                    declare @PRIMARYMEMBERID uniqueidentifier;
                    select  @PRIMARYMEMBERID = MEMBERID 
                        from dbo.GROUPMEMBER 
                        where GROUPID = @GROUPID
                        and ISPRIMARY = 1;


                    with CURRENTMEMBERS_CTE as
                    (
                        select
                            GM.ID as GROUPMEMBERID,
                            GM.MEMBERID as MEMBERID,
                            GM.ISPRIMARY as ISPRIMARY,
                            GMDR.DATEFROM as STARTDATE,
                            GMDR.DATETO as ENDDATE
                        from dbo.GROUPMEMBER GM
                        left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                        where ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                            or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                        and GM.GROUPID = @GROUPID
                    )

                    select 
                        GM.GROUPMEMBERID as GROUPMEMBERID,
                        null as GROUPMEMBERROLEID,
                        C.ID as CONSTITUENTID,
                        case when GM.ISPRIMARY = 1 
                            then 'CATALOG:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.primary_16-c.png'
                            else 'RES:lv_spacer'
                            end as ISPRIMARYIMAGE,
                        GM.ISPRIMARY as ISPRIMARY,
                        (
                            rtrim(C.NAME + ' ' +
                                coalesce(
                                    replace(
                                        stuff(
                                            (select '' + STATUS from
                                                (select ',(Deceased)' STATUS
                                                 from dbo.DECEASEDCONSTITUENT DC
                                                 where DC.ID = C.ID
                                                 union all
                                                 select ',(Inactive)' STATUS
                                                 from dbo.CONSTITUENT
                                                 where CONSTITUENT.ID = C.ID
                                                 and CONSTITUENT.ISINACTIVE=1
                                                ) as SUBQ for xml path(''))
                                        ,1,1,'')
                                    , '),(', ', ')
                                ,'')
                            )
                        ) as NAME,
                        GM.STARTDATE as STARTDATE,
                        GM.ENDDATE as ENDDATE,
                        case when GM.ISPRIMARY = 1 
                            then ''
                            else (    select 
                                        dbo.UDA_BUILDLIST(distinct RTC.DESCRIPTION)
                                    from dbo.RELATIONSHIP R
                                    join dbo.RELATIONSHIPTYPECODE RTC
                                    on R.RELATIONSHIPTYPECODEID = RTC.ID
                                    where C.ID = R.RELATIONSHIPCONSTITUENTID
                                    and R.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID
                                    )
                            end as RELATIONSHIPWITHPRIMARY,
                        null as PARENT,
                        row_number() over (order by GM.ISPRIMARY desc, C.NAME) as SEQUENCE,
                        0 as ISROLE,
                        GM.GROUPMEMBERID as CONTEXTMEMBERID
                    from CURRENTMEMBERS_CTE as GM
                    inner join dbo.CONSTITUENT as C on GM.MEMBERID = C.ID

                    union all

                    select
                        null as GROUPMEMBERID,
                        GMR.ID as GROUPMEMBERROLEID,
                        C.ID as CONSTITUENTID,
                        'RES:lv_spacer' as ISPRIMARYIMAGE,
                        0 as ISPRIMARY,
                        GMRC.DESCRIPTION as NAME,
                        GMR.STARTDATE as STARTDATE,
                        GMR.ENDDATE as ENDDATE,
                        null as RELATIONSHIPWITHPRIMARY,
                        GMR.GROUPMEMBERID as PARENT,
                        row_number() over (order by GMRC.DESCRIPTION) as SEQUENCE,
                        1 as ISROLE,
                        GMR.GROUPMEMBERID as CONTEXTMEMBERID
                    from dbo.GROUPMEMBERROLE as GMR
                    inner join CURRENTMEMBERS_CTE as GM on GMR.GROUPMEMBERID = GM.GROUPMEMBERID
                    inner join dbo.CONSTITUENT as C on GM.MEMBERID = C.ID
                    inner join dbo.GROUPMEMBERROLECODE as GMRC on GMR.GROUPMEMBERROLECODEID = GMRC.ID