USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPWEALTHSUMMARY

The load procedure used by the view dataform template "Research Group Wealth Summary View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MATCHEDPROSPECTS	int	INOUT	Matched prospects
@PROCESSEDON	datetime	INOUT	Processed on
@MATCHRATE	decimal(5, 2)	INOUT	Match rate
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESEARCHGROUPWEALTHSUMMARY (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @MATCHEDPROSPECTS int = null output,
                    @PROCESSEDON datetime = null output,
                    @MATCHRATE decimal(5,2) = null output
                ) with execute as owner as
                    set nocount on;

                    declare @TOTALPROSPECTS int;
                    set @TOTALPROSPECTS = 0;
                    set @MATCHEDPROSPECTS = 0;

                    select
                        @DATALOADED = 1
                    from
                        dbo.RESEARCHGROUP RG
                        left join dbo.APPUSER on RG.OWNERID = APPUSER.ID
                    where
                        RG.ID = @ID;

                    select top(1)
                        @PROCESSEDON = WPSEARCHHISTORY.DATEADDED
                    from
                        dbo.WPSEARCHHISTORY
                    where
                        WPSEARCHHISTORY.RESEARCHGROUPID = @ID
                    order by
                        WPSEARCHHISTORY.DATEADDED desc;

                    select
                        @TOTALPROSPECTS = coalesce(count(MATCHED.CONSTITUENTID),0),
                        @MATCHEDPROSPECTS  = coalesce(sum(MATCHED.ISMATCHED),0)
                    from (
                        select
                            RGM.CONSTITUENTID,
                            case when
                            (select top 1 WPAFFLUENCEINDICATOR.ID from dbo.WPAFFLUENCEINDICATOR where WPAFFLUENCEINDICATOR.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPBIOGRAPHICAL.ID from dbo.WPBIOGRAPHICAL where WPBIOGRAPHICAL.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPBUSINESSOWNERSHIP.ID from dbo.WPBUSINESSOWNERSHIP where WPBUSINESSOWNERSHIP.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPINCOMECOMPENSATION.ID from dbo.WPINCOMECOMPENSATION where WPINCOMECOMPENSATION.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPNONPROFITAFFILIATION.ID from dbo.WPNONPROFITAFFILIATION where WPNONPROFITAFFILIATION.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPOTHERASSET.ID from dbo.WPOTHERASSET where WPOTHERASSET.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPPHILANTHROPICGIFT.ID from dbo.WPPHILANTHROPICGIFT where WPPHILANTHROPICGIFT.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPPOLITICALDONATION.ID from dbo.WPPOLITICALDONATION where WPPOLITICALDONATION.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPPRIVATEFOUNDATION.ID from dbo.WPPRIVATEFOUNDATION where WPPRIVATEFOUNDATION.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPREALESTATE.ID from dbo.WPREALESTATE where WPREALESTATE.WEALTHID = RGM.CONSTITUENTID) is not null or
                            (select top 1 WPSECURITIES.ID from dbo.WPSECURITIES where WPSECURITIES.WEALTHID = RGM.CONSTITUENTID) is not null
                            then 1 else 0 end ISMATCHED,
                            WEALTH.WEALTHPOINTDATE
                        from
                            dbo.RESEARCHGROUPMEMBER RGM
                        left join
                            dbo.WEALTH on WEALTH.ID = RGM.ID
                        where
                            RGM.RESEARCHGROUPID = @ID
                        group by
                            RGM.CONSTITUENTID, WEALTH.WEALTHPOINTDATE) as MATCHED;

                    set    @MATCHRATE = case when @TOTALPROSPECTS > 0 then convert(decimal,@MATCHEDPROSPECTS) / convert(decimal,@TOTALPROSPECTS) else 0.0 end;

                    if @MATCHRATE > 0.0
                        set    @MATCHRATE = @MATCHRATE * convert(decimal,100)