USP_RECORDOPERATION_PROSPECTSEGMENTATION_UPDATE
Executes the "Prospect Segmentation: Update" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being updated. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the update. |
Definition
Copy
create procedure dbo.USP_RECORDOPERATION_PROSPECTSEGMENTATION_UPDATE (
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null
) as begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--Clear any existing members
exec dbo.USP_PROSPECTSEGMENTATION_PROSPECTS_CLEAR @ID, @CHANGEAGENTID
--Populate some variables about the Prospect Segmentation
declare @RESEARCHGROUPID uniqueidentifier;
declare @IDSETREGISTERID uniqueidentifier;
select
@RESEARCHGROUPID = RESEARCHGROUPID,
@IDSETREGISTERID = IDSETREGISTERID
from
PROSPECTSEGMENTATION
where
ID = @ID
-- Populate table variable of Prospects
declare @PROSPECTS as table(CONSTITUENTID uniqueidentifier)
--Select research group members
if @RESEARCHGROUPID is not null
begin
insert into @PROSPECTS
select
CONSTITUENTID
from
dbo.RESEARCHGROUPMEMBER
where
RESEARCHGROUPID = @RESEARCHGROUPID
end
--Select selection members
if @IDSETREGISTERID is not null
begin
insert into @PROSPECTS
select
ID
from
dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID)
end
--Select all modeled constituents
if @RESEARCHGROUPID is null and @IDSETREGISTERID is null
begin
insert into @PROSPECTS
select
ID
from
dbo.MODELINGANDPROPENSITY
end;
-- Get section information
with SECTIONS(
ID,
SEQUENCE,
RANGELOW,
RANGEHIGH,
ESTIMATEDWEALTHLOWID,
ESTIMATEDWEALTHHIGHID,
MAJORGIVINGCAPACITYLOWID,
MAJORGIVINGCAPACITYHIGHID,
WEALTHESTIMATORRATINGLOWID,
WEALTHESTIMATORRATINGHIGHID,
TARGETGIFTRANGELOWID,
TARGETGIFTRANGEHIGHID,
SECTIONTYPE,
VARIABLETYPE
)as(
select
SECTION.ID,
SECTION.SEQUENCE,
nullif(SECTION.RANGELOW,-1) as RANGELOW,
nullif(SECTION.RANGEHIGH,-1) as RANGEHIGH,
SECTION.ESTIMATEDWEALTHLOWID,
SECTION.ESTIMATEDWEALTHHIGHID,
SECTION.MAJORGIVINGCAPACITYLOWID,
SECTION.MAJORGIVINGCAPACITYHIGHID,
SECTION.WEALTHESTIMATORRATINGLOWID,
SECTION.WEALTHESTIMATORRATINGHIGHID,
SECTION.TARGETGIFTRANGELOWID,
SECTION.TARGETGIFTRANGEHIGHID,
case VARIABLE.ISPRIMARY
when 1 then 1
when 0 then 2
end as SECTIONTYPE,
VARIABLE.TYPECODE
from
PROSPECTSEGMENTATIONSECTION SECTION
left join PROSPECTSEGMENTATIONVARIABLE VARIABLE on
VARIABLE.ID = SECTION.VARIABLEID
where
VARIABLE.PROSPECTSEGMENTATIONID = @ID
union all
select
SECTION.ID,
SECTION.SEQUENCE,
nullif(SECTION.RANGE2LOW,-1) as RANGELOW,
nullif(SECTION.RANGE2HIGH,-1) as RANGEHIGH,
SECTION.ESTIMATEDWEALTH2LOWID,
SECTION.ESTIMATEDWEALTH2HIGHID,
SECTION.MAJORGIVINGCAPACITY2LOWID,
SECTION.MAJORGIVINGCAPACITY2HIGHID,
SECTION.WEALTHESTIMATORRATING2LOWID,
SECTION.WEALTHESTIMATORRATING2HIGHID,
SECTION.TARGETGIFTRANGE2LOWID,
SECTION.TARGETGIFTRANGE2HIGHID,
3,
VARIABLE.TYPE2CODE
from
PROSPECTSEGMENTATIONSECTION SECTION
left join PROSPECTSEGMENTATIONVARIABLE VARIABLE on
VARIABLE.ID = SECTION.VARIABLEID
where
VARIABLE.PROSPECTSEGMENTATIONID = @ID and
VARIABLE.ISPRIMARY = 0 and
SECTION.LABEL2 != ''
),
SECTIONSWITHDATA(
ID,
SEQUENCE,
RANGELOW,
RANGEHIGH,
ESTIMATEDWEALTHLOW,
ESTIMATEDWEALTHHIGH,
MAJORGIVINGCAPACITYLOW,
MAJORGIVINGCAPACITYHIGH,
WEALTHESTIMATORRATINGLOW,
WEALTHESTIMATORRATINGHIGH,
TARGETGIFTRANGELOW,
TARGETGIFTRANGEHIGH,
SECTIONTYPE,
VARIABLETYPE
)as(
select
SECTIONS.ID,
SECTIONS.SEQUENCE,
SECTIONS.RANGELOW,
SECTIONS.RANGEHIGH,
ESTIMATEDWEALTHLOW.MINIMUMAMOUNT,
ESTIMATEDWEALTHHIGH.MINIMUMAMOUNT,
MAJORGIVINGCAPACITYLOW.MINIMUMAMOUNT,
MAJORGIVINGCAPACITYHIGH.MINIMUMAMOUNT,
WEALTHESTIMATORRATINGLOW.MINIMUMAMOUNT,
WEALTHESTIMATORRATINGHIGH.MINIMUMAMOUNT,
TARGETGIFTRANGELOW.MINIMUMAMOUNT,
TARGETGIFTRANGEHIGH.MINIMUMAMOUNT,
SECTIONS.SECTIONTYPE,
SECTIONS.VARIABLETYPE
from
SECTIONS
left join dbo.ESTIMATEDWEALTH ESTIMATEDWEALTHLOW on
ESTIMATEDWEALTHLOW.ID = SECTIONS.ESTIMATEDWEALTHLOWID
left join dbo.ESTIMATEDWEALTH ESTIMATEDWEALTHHIGH on
ESTIMATEDWEALTHHIGH.ID = SECTIONS.ESTIMATEDWEALTHHIGHID
left join dbo.MAJORGIVINGCAPACITY MAJORGIVINGCAPACITYLOW on
MAJORGIVINGCAPACITYLOW.ID = SECTIONS.MAJORGIVINGCAPACITYLOWID
left join dbo.MAJORGIVINGCAPACITY MAJORGIVINGCAPACITYHIGH on
MAJORGIVINGCAPACITYHIGH.ID = SECTIONS.MAJORGIVINGCAPACITYHIGHID
left join dbo.WEALTHESTIMATORRATING WEALTHESTIMATORRATINGLOW on
WEALTHESTIMATORRATINGLOW.ID = SECTIONS.WEALTHESTIMATORRATINGLOWID
left join dbo.WEALTHESTIMATORRATING WEALTHESTIMATORRATINGHIGH on
WEALTHESTIMATORRATINGHIGH.ID = SECTIONS.WEALTHESTIMATORRATINGHIGHID
left join dbo.TARGETGIFTRANGE TARGETGIFTRANGELOW on
TARGETGIFTRANGELOW.ID = SECTIONS.TARGETGIFTRANGELOWID
left join dbo.TARGETGIFTRANGE TARGETGIFTRANGEHIGH on
TARGETGIFTRANGEHIGH.ID = SECTIONS.TARGETGIFTRANGEHIGHID
)
--Update prospects table
insert into
dbo.PROSPECTSEGMENTATIONPROSPECT
(
CONSTITUENTID,
PRIMARYSECTIONID,
SECONDARYSECTIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
--Select best match
select
CONSTITUENTID,
PRIMARYSECTIONS.ID as PRIMARYSECTIONID,
case
when isnull(SECONDARYSECTIONS1.SEQUENCE,0) >= isnull(SECONDARYSECTIONS2.SEQUENCE,0) then
SECONDARYSECTIONS1.ID
when isnull(SECONDARYSECTIONS1.SEQUENCE,0) < isnull(SECONDARYSECTIONS2.SEQUENCE,0) then
SECONDARYSECTIONS2.ID
end as SECONDARYSECTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@PROSPECTS
--Join to scoring tables
left join dbo.WEALTHCAPACITY on
WEALTHCAPACITY.ID = CONSTITUENTID
left join dbo.MODELINGANDPROPENSITY on
MODELINGANDPROPENSITY.ID = CONSTITUENTID
--Join to range tables
left join dbo.ESTIMATEDWEALTH on
ESTIMATEDWEALTH.ID = WEALTHCAPACITY.ESTIMATEDWEALTHID
left join dbo.MAJORGIVINGCAPACITY on
MAJORGIVINGCAPACITY.ID = WEALTHCAPACITY.MAJORGIVINGCAPACITYID
left join dbo.TARGETGIFTRANGE on
TARGETGIFTRANGE.ID = MODELINGANDPROPENSITY.TARGETGIFTRANGEID
left join dbo.WEALTHESTIMATORRATING on
WEALTHESTIMATORRATING.ID = MODELINGANDPROPENSITY.WEALTHESTIMATORRATINGID
--Join to sections
left join SECTIONSWITHDATA PRIMARYSECTIONS on
PRIMARYSECTIONS.SECTIONTYPE = 1 and
((PRIMARYSECTIONS.VARIABLETYPE = 1 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 2 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 3 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 4 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.CRTLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.CRTLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 5 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 6 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 7 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 8 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD) or
(PRIMARYSECTIONS.VARIABLETYPE = 9 and PRIMARYSECTIONS.RANGELOW <= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD and
PRIMARYSECTIONS.RANGEHIGH >= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD) or
(PRIMARYSECTIONS.ESTIMATEDWEALTHLOW <= ESTIMATEDWEALTH.MINIMUMAMOUNT and PRIMARYSECTIONS.ESTIMATEDWEALTHHIGH >= ESTIMATEDWEALTH.MINIMUMAMOUNT) or
(PRIMARYSECTIONS.MAJORGIVINGCAPACITYLOW <= MAJORGIVINGCAPACITY.MINIMUMAMOUNT and PRIMARYSECTIONS.MAJORGIVINGCAPACITYHIGH >= MAJORGIVINGCAPACITY.MINIMUMAMOUNT) or
(PRIMARYSECTIONS.TARGETGIFTRANGELOW <= TARGETGIFTRANGE.MINIMUMAMOUNT and PRIMARYSECTIONS.TARGETGIFTRANGEHIGH >= TARGETGIFTRANGE.MINIMUMAMOUNT) or
(PRIMARYSECTIONS.WEALTHESTIMATORRATINGLOW <= WEALTHESTIMATORRATING.MINIMUMAMOUNT and PRIMARYSECTIONS.WEALTHESTIMATORRATINGHIGH >= WEALTHESTIMATORRATING.MINIMUMAMOUNT))
left join SECTIONSWITHDATA SECONDARYSECTIONS1 on
SECONDARYSECTIONS1.SECTIONTYPE = 2 and
((SECONDARYSECTIONS1.VARIABLETYPE = 1 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 2 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 3 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 4 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.CRTLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.CRTLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 5 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 6 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 7 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 8 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD) or
(SECONDARYSECTIONS1.VARIABLETYPE = 9 and SECONDARYSECTIONS1.RANGELOW <= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD and
SECONDARYSECTIONS1.RANGEHIGH >= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD) or
(SECONDARYSECTIONS1.ESTIMATEDWEALTHLOW <= ESTIMATEDWEALTH.MINIMUMAMOUNT and SECONDARYSECTIONS1.ESTIMATEDWEALTHHIGH >= ESTIMATEDWEALTH.MINIMUMAMOUNT) or
(SECONDARYSECTIONS1.MAJORGIVINGCAPACITYLOW <= MAJORGIVINGCAPACITY.MINIMUMAMOUNT and SECONDARYSECTIONS1.MAJORGIVINGCAPACITYHIGH >= MAJORGIVINGCAPACITY.MINIMUMAMOUNT) or
(SECONDARYSECTIONS1.TARGETGIFTRANGELOW <= TARGETGIFTRANGE.MINIMUMAMOUNT and SECONDARYSECTIONS1.TARGETGIFTRANGEHIGH >= TARGETGIFTRANGE.MINIMUMAMOUNT) or
(SECONDARYSECTIONS1.WEALTHESTIMATORRATINGLOW <= WEALTHESTIMATORRATING.MINIMUMAMOUNT and SECONDARYSECTIONS1.WEALTHESTIMATORRATINGHIGH >= WEALTHESTIMATORRATING.MINIMUMAMOUNT))
left join SECTIONSWITHDATA SECONDARYSECTIONS2 on
SECONDARYSECTIONS2.SECTIONTYPE = 3 and
((SECONDARYSECTIONS2.VARIABLETYPE = 1 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 2 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 3 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 4 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.CRTLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.CRTLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 5 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 6 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 7 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 8 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD) or
(SECONDARYSECTIONS2.VARIABLETYPE = 9 and SECONDARYSECTIONS2.RANGELOW <= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD and
SECONDARYSECTIONS2.RANGEHIGH >= MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD) or
(SECONDARYSECTIONS2.ESTIMATEDWEALTHLOW <= ESTIMATEDWEALTH.MINIMUMAMOUNT and SECONDARYSECTIONS2.ESTIMATEDWEALTHHIGH >= ESTIMATEDWEALTH.MINIMUMAMOUNT) or
(SECONDARYSECTIONS2.MAJORGIVINGCAPACITYLOW <= MAJORGIVINGCAPACITY.MINIMUMAMOUNT and SECONDARYSECTIONS2.MAJORGIVINGCAPACITYHIGH >= MAJORGIVINGCAPACITY.MINIMUMAMOUNT) or
(SECONDARYSECTIONS2.TARGETGIFTRANGELOW <= TARGETGIFTRANGE.MINIMUMAMOUNT and SECONDARYSECTIONS2.TARGETGIFTRANGEHIGH >= TARGETGIFTRANGE.MINIMUMAMOUNT) or
(SECONDARYSECTIONS2.WEALTHESTIMATORRATINGLOW <= WEALTHESTIMATORRATING.MINIMUMAMOUNT and SECONDARYSECTIONS2.WEALTHESTIMATORRATINGHIGH >= WEALTHESTIMATORRATING.MINIMUMAMOUNT))
where
PRIMARYSECTIONS.ID is not null and
(SECONDARYSECTIONS1.ID is not null or SECONDARYSECTIONS2.ID is not null)
group by
PRIMARYSECTIONS.ID,
case
when isnull(SECONDARYSECTIONS1.SEQUENCE,0) >= isnull(SECONDARYSECTIONS2.SEQUENCE,0) then
SECONDARYSECTIONS1.ID
when isnull(SECONDARYSECTIONS1.SEQUENCE,0) < isnull(SECONDARYSECTIONS2.SEQUENCE,0) then
SECONDARYSECTIONS2.ID
end,
CONSTITUENTID;
--Finally, Update the Prospect Segmentation
Update
dbo.PROSPECTSEGMENTATION
set
STATUSCODE = 0,
DATEUPDATED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
return 0;
end