USP_DATALIST_GROUPMEMBERCURRENT

                CREATE procedure dbo.USP_DATALIST_GROUPMEMBERCURRENT
                (
                    @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;

                    select 
                        GM.ID as GROUPMEMBERID,
                        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(NF.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,
                        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
                    from dbo.GROUPMEMBER as GM
                    join dbo.CONSTITUENT as C on GM.MEMBERID = C.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    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
                    order by GM.ISPRIMARY desc, C.KEYNAME