USP_DATALIST_CONSTITUENTGROUP_MEMBERLISTSUMMARY

CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_MEMBERLISTSUMMARY
(
  @GROUPID uniqueidentifier 
)
as
  set nocount on;

  declare @CURRENTDATE date;
  set @CURRENTDATE = getdate();

  declare @PRIMARYMEMBERID uniqueidentifier;
  select  @PRIMARYMEMBERID = GM.MEMBERID 
  from dbo.GROUPMEMBER GM
  left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
  where GM.GROUPID = @GROUPID
  and GM.ISPRIMARY = 1
  and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
  or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
  or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));

  select top 3
    GM.ID as GROUPMEMBERID,
    GM.MEMBERID as CONSTITUENTID,
    case
      when GM.ISPRIMARY = 1 then 'CATALOG:Blackbaud.AppFx.ConstituentGroups.Catalog.dll,Blackbaud.AppFx.ConstituentGroups.Catalog.primary_16-c.png'
      else ''
    end,
    GM.ISPRIMARY as ISPRIMARY,
    NF.NAME 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 GM.MEMBERID = R.RELATIONSHIPCONSTITUENTID
          and R.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID
        )
    end as RELATIONSHIPWITHPRIMARY,
    (select dbo.UDA_BUILDLIST(distinct DESCRIPTION) from 
      dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(GM.MEMBERID) SUBLIST 
      where 
      (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) is null and (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) is null or dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) >= @CURRENTDATE))
      or (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) is null and (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) is null or dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) <= @CURRENTDATE)) 
      or (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) <= @CURRENTDATE and dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) >= @CURRENTDATE))
    as ACTIVECONSTITUENCIES
  from
    dbo.GROUPMEMBER GM
  left join
    dbo.GROUPMEMBERDATERANGE as GMDR on GMDR.GROUPMEMBERID = GM.ID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) NF
  where
    GM.GROUPID = @GROUPID and
    ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
      or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
      or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
  order by
    ISPRIMARY desc