USP_WEALTHPOINT_GETRESEARCHGROUPINVALIDCONSTITUENTS

            create procedure dbo.USP_WEALTHPOINT_GETRESEARCHGROUPINVALIDCONSTITUENTS(
                @ID uniqueidentifier
            ) as begin
                set nocount on;

                select
                    C.ID,
                    C.NAME,
                    case when C.ISORGANIZATION = 1 then 0 when not DWU.ID is null then 1 else 2 end
                from
                    dbo.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                where
                    RG.ID = @ID and
                    (C.ISORGANIZATION = 1 or
                    not DWU.ID is null);

                return 0;

            end;