USP_DATALIST_CONSTITUENTGROUP_PROSPECT_PLANNEDGIFTSACCEPTED

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_PROSPECT_PLANNEDGIFTSACCEPTED
        (
          @PROSPECTID uniqueidentifier
        )
                as begin
                    set nocount on;

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

          declare @MEMBERTABLE table
          (
              ID uniqueidentifier
          );

          insert into @MEMBERTABLE
            select @PROSPECTID
            union all
              select 
                  MEMBERID 
              from dbo.GROUPMEMBER GM
              left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
              where GM.GROUPID=@PROSPECTID
              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
                        PG.ID,
            (select NAME from dbo.CONSTITUENT where CONSTITUENT.ID = PG.ID),
                        PG.VEHICLE,
                        PG.GIFTDATE,
                        PG.GIFTAMOUNT,
                        PG.ISANONYMOUS,
                        PG.ISREVOCABLE,
            case 
                  when PG.CONSTITUENTID = @PROSPECTID then '0'
                  when (select ISPRIMARY from GROUPMEMBER where GROUPID=@PROSPECTID and MEMBERID=PG.CONSTITUENTID) = 1 then '1'
                  else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=PG.CONSTITUENTID)
              end as 'GROUPORPRIMARYSORT'
                    from
                        dbo.PLANNEDGIFT PG
                    where
                        PG.CONSTITUENTID in (select ID from @MEMBERTABLE)
                        and STATUSCODE = 2
                    order by
                        PG.GIFTDATE
                end