USP_DATALIST_GROUPMEMBERWITHROLEPREVIOUS

CREATE procedure dbo.USP_DATALIST_GROUPMEMBERWITHROLEPREVIOUS
(
    @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 PREVIOUSMEMBERS_CTE as
    (
        select
            GM.ID as GROUPMEMBERID,
            GM.MEMBERID as MEMBERID,
            GM.ISPRIMARY as ISPRIMARY,
            GMDR.ID as GROUPMEMBERDATERANGEID,
            GMDR.DATEFROM as STARTDATE,
            GMDR.DATETO as ENDDATE,
            GMDR.COMMENTS as COMMENTS
        from dbo.GROUPMEMBER GM
        left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
        where (GMDR.DATETO is not null and GMDR.DATETO <= @CURRENTDATEEARLIESTTIME)
        and GM.ID not in (select GROUPMEMBERID from dbo.GROUPMEMBERDATERANGE where DATETO is null)
        and GM.GROUPID = @GROUPID
    )

    select 
        GM.GROUPMEMBERID as GROUPMEMBERID,
        GM.GROUPMEMBERDATERANGEID as GROUPMEMBERDATERANGEID,
        null as GROUPMEMBERROLEID,
        C.ID as CONSTITUENTID,
        (
            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,
        GM.COMMENTS as COMMENTS,
        null as PARENT,
        row_number() over (order by GM.STARTDATE, GM.ENDDATE) as SEQUENCE,
        0 as ISROLE,
        GM.GROUPMEMBERID as CONTEXTMEMBERID
    from PREVIOUSMEMBERS_CTE as GM
    inner join dbo.CONSTITUENT as C on GM.MEMBERID = C.ID

    union all

    select
        null as GROUPMEMBERID,
        null as GROUPMEMBERDATERANGEID,
        GMR.ID as GROUPMEMBERROLEID,
        C.ID as CONSTITUENTID,
        GMRC.DESCRIPTION as NAME,
        GMR.STARTDATE as STARTDATE,
        GMR.ENDDATE as ENDDATE,
        null as RELATIONSHIPWITHPRIMARY,
        null as COMMENTS,
        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 PREVIOUSMEMBERS_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