USP_DATALIST_GROUPMEMBER

CREATE procedure dbo.USP_DATALIST_GROUPMEMBER(
    @GROUPID uniqueidentifier,
    @INCLUDEPREVIOUSMEMBERS bit = 0
)
as
    set nocount on;

    declare @CURRENTDATEEARLIESTTIME date = getdate();

    declare @PRIMARYMEMBERID uniqueidentifier;

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

    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
        from dbo.GROUPMEMBER
            left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
        where GROUPMEMBER.GROUPID = @GROUPID 
            and(
                @INCLUDEPREVIOUSMEMBERS = 1
                or(
                    (GROUPMEMBERDATERANGE.DATEFROM is null 
                        and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIESTTIME)
                    ) 
                    or(GROUPMEMBERDATERANGE.DATETO is null 
                        and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
                    ) 
                    or(GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME 
                        and GROUPMEMBERDATERANGE.DATETO > @CURRENTDATEEARLIESTTIME)
                    )
            )
    )
    select
        CURRENTGROUPMEMBER.GROUPMEMBERID as GROUPMEMBERID,
        null,
        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 = @PRIMARYMEMBERID
            )
        end as RELATIONSHIPWITHPRIMARY,
        null,
        row_number() over (order by CURRENTGROUPMEMBER.ISPRIMARY desc, DISPLAYNAME.NAME) as SEQUENCE,
        0,
        CURRENTGROUPMEMBER.GROUPMEMBERID as CONTEXTMEMBERID,
        CURRENTGROUPMEMBER.COMMENTS as COMMENTS
    from CTE_CURRENTGROUPMEMBER as CURRENTGROUPMEMBER
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CURRENTGROUPMEMBER.MEMBERID) DISPLAYNAME
    order by SEQUENCE