USP_PROSPECTSEGMENTATION_PROSPECTS

This stored procedure selects all the prospects associated with a Prospect Segmentation segment.
Parameters

Parameter	Parameter Type	Mode	Description
@PRIMARYSECTIONID	uniqueidentifier	IN
@SECONDARYSECTIONID	uniqueidentifier	IN
Definition

Copy


      create procedure dbo.USP_PROSPECTSEGMENTATION_PROSPECTS(
        @PRIMARYSECTIONID uniqueidentifier,
                @SECONDARYSECTIONID uniqueidentifier        
      )as
        set nocount on;

                select
            PROSPECT.CONSTITUENTID CONSTITUENTID,
                    CONSTITUENT.LOOKUPID LOOKUPID,
                    CONSTITUENT.NAME NAME,
                    ADDRESS.ADDRESSBLOCK ADDRESS,
                    ADDRESS.CITY CITY,
                    STATE.DESCRIPTION STATE,
                    ADDRESS.POSTCODE POSTCODE,
                    nullif(MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD,-1) ANNUALGIVINGLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD,-1) ANNUITYLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD,-1) BEQUESTLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.CRTLIKELIHOOD,-1) CRTLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD,-1) MAJORGIVINGLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD,-1) MEMBERSHIPLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD,-1) ONLINEGIVINGLIKELIHOOD,
                    nullif(MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD,-1) PATIENTRESPONSELIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD,-1) PLANNEDGIVINGLIKELIHOOD,
                    WEALTHCAPACITY.ESTIMATEDWEALTHID,
                    WEALTHCAPACITY.MAJORGIVINGCAPACITYID,
                    MODELINGANDPROPENSITY.TARGETGIFTRANGEID,
                    MODELINGANDPROPENSITY.WEALTHESTIMATORRATINGID

                from
                    dbo.PROSPECTSEGMENTATIONPROSPECT PROSPECT
        left join dbo.CONSTITUENT on
                    CONSTITUENT.ID = PROSPECT.CONSTITUENTID
                left join dbo.ADDRESS on
                    ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                left join dbo.STATE on
                    STATE.ID = ADDRESS.STATEID

                --Join to scoring tables

                left join dbo.WEALTHCAPACITY on
                    WEALTHCAPACITY.ID = PROSPECT.CONSTITUENTID
                left join dbo.MODELINGANDPROPENSITY on
                    MODELINGANDPROPENSITY.ID = PROSPECT.CONSTITUENTID

                where
          PROSPECT.PRIMARYSECTIONID   = @PRIMARYSECTIONID and
                   PROSPECT.SECONDARYSECTIONID = @SECONDARYSECTIONID