USP_DATALIST_RESEARCHGROUPMEMBERS

                CREATE procedure dbo.USP_DATALIST_RESEARCHGROUPMEMBERS (
                    @ID uniqueidentifier,
                    @NAME nvarchar(154) = null,
                    @CITY nvarchar(100) = null,
                    @STATEID uniqueidentifier = null,
                    @HASWEALTHINFORMATION bit = 1,
                    @CONFIRMEDASSETSMINIMUMVALUE money = null,
                    @ESTIMATEDWEALTHMINIMUMVALUE money = null
                ) as
                    set nocount on;

                    with WEALTHINFORMATION(ID) as
                    (
                        select distinct WP.WEALTHID from dbo.WPAFFLUENCEINDICATOR WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPBIOGRAPHICAL WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPBUSINESSOWNERSHIP WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                         select distinct WP.WEALTHID from dbo.WPINCOMECOMPENSATION WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPNONPROFITAFFILIATION WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPOTHERASSET WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPPHILANTHROPICGIFT WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPPOLITICALDONATION WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPPRIVATEFOUNDATION WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPREALESTATE WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID union
                        select distinct WP.WEALTHID from dbo.WPSECURITIES WP left join dbo.RESEARCHGROUPMEMBER RGM on RGM.CONSTITUENTID = WP.WEALTHID where RGM.RESEARCHGROUPID = @ID
                    )
                    select
                        RESEARCHGROUPMEMBER.ID,
                        CONSTITUENT.ID CONSTITUENTID,
                        NF.NAME,
                        ADDRESS.ADDRESSBLOCK,
                        ADDRESS.CITY,
                        STATE.DESCRIPTION,
                        ADDRESS.POSTCODE,
                        CONSTITUENT.LOOKUPID,
                        case when WEALTHINFORMATION.ID is null then 0 else 1 end MATCHED,
                        WEALTH.WEALTHPOINTDATE,
                        case when DISABLEDWEALTHUPDATES.ID is null then 0 else 1 end DISABLEDWEALTHUPDATES,
                        WEALTH.PENDINGSEARCH UPDATEPENDING,
                        case when len(CONSTITUENT.FIRSTNAME)>0 and CONSTITUENT.ISORGANIZATION=0 then 1 else 0 end VALIDSEARCHCRITERIARELAXED,
                        WEALTH.TOTALASSETSCONFIRMED,
                        WEALTHCAPACITY.ESTIMATEDWEALTHVALUE
                    from dbo.RESEARCHGROUPMEMBER
                    left join dbo.CONSTITUENT on CONSTITUENT.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    left join dbo.STATE on STATE.ID = ADDRESS.STATEID
                    left join dbo.WEALTH on WEALTH.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    left join WEALTHINFORMATION on WEALTHINFORMATION.ID = WEALTH.ID 
                    left join dbo.WEALTHCAPACITY on WEALTHCAPACITY.ID = CONSTITUENT.ID
                    left join dbo.DISABLEDWEALTHUPDATES on DISABLEDWEALTHUPDATES.ID = CONSTITUENT.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                    where
                        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @ID and
                        ((@NAME is null or @NAME = '') or CONSTITUENT.NAME like '%' + @NAME + '%') and
                        ((@CITY is null or @CITY = '') or ADDRESS.CITY like '%' + @CITY + '%') and
                        (@STATEID is null or @STATEID = STATE.ID) and
                        (@HASWEALTHINFORMATION = 0 or WEALTHINFORMATION.ID is not null) and
                        ((@CONFIRMEDASSETSMINIMUMVALUE is null) or WEALTH.TOTALASSETSCONFIRMED > @CONFIRMEDASSETSMINIMUMVALUE) and
                        ((@ESTIMATEDWEALTHMINIMUMVALUE is null) or WEALTHCAPACITY.ESTIMATEDWEALTHVALUE > @ESTIMATEDWEALTHMINIMUMVALUE)
                    order by
                        CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME;