USP_MERGETASK_CONSTITUENTWEALTHANDRATINGS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
-- Named SP and file Wealth and Ratings since eventually this will merge all wealth and rating data.
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTWEALTHANDRATINGS
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @DATECHANGED datetime = getdate();
/* Batch - do batch before score merge so source scores can be compared in update batch */
-- If the source record exists in any Model Scores and Ratings Batches or Model Scores and Ratings Update Batches, change the batch row to have the target constituent instead.
update
dbo.BATCHMODELINGANDPROPENSITY
set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID;
-- For the modeling and propensity update batch, compare the score values for built-in models to the score values from the source constituent record.
-- If any of the batch score values are the same as the source score values, then update the batch to instead have the current score value of the target constituent.
-- These score value represent data that has not been updated by the batch data entry user, so we make this adjustment to keep that intention the same.
-- This does not apply to attribute values since those are added by the batch rather than updated.
-- This does not apply to the non-update Model Scores and Ratings Batch since committing those scores would overwrite any values as intended by the behavior of that batch type.
update
dbo.BATCHMODELINGANDPROPENSITYUPDATE
set
BATCHMODELINGANDPROPENSITYUPDATE.PRIMARYRECORDID = @TARGETID,
BATCHMODELINGANDPROPENSITYUPDATE.LOOKUP_ID = CONSTITUENT.LOOKUPID,
BATCHMODELINGANDPROPENSITYUPDATE.KEYNAME = CONSTITUENT.KEYNAME,
BATCHMODELINGANDPROPENSITYUPDATE.FIRSTNAME = CONSTITUENT.FIRSTNAME,
BATCHMODELINGANDPROPENSITYUPDATE.ANNUALGIFTLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.ANNUALGIFTLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.ANNUALGIFTLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.ANNUITYLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.ANNUITYLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.ANNUITYLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.ANNUITYLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.ANNUITYLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.ANNUITYLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.BEQUESTLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.BEQUESTLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.BEQUESTLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.BEQUESTLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.BEQUESTLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.BEQUESTLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.CRTLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.CRTLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.CRTLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.CRTLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.CRTLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.CRTLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.MAJORGIVINGLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.MAJORGIVINGLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.MAJORGIVINGLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.MEMBERSHIPLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.MEMBERSHIPLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.MEMBERSHIPLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.ONLINEGIVINGLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.ONLINEGIVINGLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.ONLINEGIVINGLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.PATIENTRESPONSELIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.PATIENTRESPONSELIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.PATIENTRESPONSELIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.PLANNEDGIFTLIKELIHOOD =
case
when BATCHMODELINGANDPROPENSITYUPDATE.PLANNEDGIFTLIKELIHOOD = coalesce(CURRENTMODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1)
then coalesce(nullif(TARGETMODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1), SOURCEMODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1)
else
BATCHMODELINGANDPROPENSITYUPDATE.PLANNEDGIFTLIKELIHOOD
end,
BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELID =
case
when BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELID = CURRENTMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID
or BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELID is null and CURRENTMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID is null
then coalesce(TARGETMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID, SOURCEMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID)
else
BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELID
end,
BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELSCOREID =
case
when BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELSCOREID = CURRENTMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID
or BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELSCOREID is null and CURRENTMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID is null
then coalesce(TARGETMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID, SOURCEMODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID)
else
BATCHMODELINGANDPROPENSITYUPDATE.SUGGESTEDMEMBERSHIPLEVELSCOREID
end,
BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGEID =
case
when BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGEID = CURRENTMODELINGANDPROPENSITY.TARGETGIFTRANGEID
or BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGEID is null and CURRENTMODELINGANDPROPENSITY.TARGETGIFTRANGEID is null
then coalesce(TARGETMODELINGANDPROPENSITY.TARGETGIFTRANGEID, SOURCEMODELINGANDPROPENSITY.TARGETGIFTRANGEID)
else
BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGEID
end,
BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGESCOREID =
case
when BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGESCOREID = CURRENTMODELINGANDPROPENSITY.TARGETGIFTRANGEID
or BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGESCOREID is null and CURRENTMODELINGANDPROPENSITY.TARGETGIFTRANGEID is null
then coalesce(TARGETMODELINGANDPROPENSITY.TARGETGIFTRANGEID, SOURCEMODELINGANDPROPENSITY.TARGETGIFTRANGEID)
else
BATCHMODELINGANDPROPENSITYUPDATE.TARGETGIFTRANGESCOREID
end,
BATCHMODELINGANDPROPENSITYUPDATE.CHANGEDBYID = @CHANGEAGENTID,
BATCHMODELINGANDPROPENSITYUPDATE.DATECHANGED = @DATECHANGED
from
dbo.BATCHMODELINGANDPROPENSITYUPDATE
inner join dbo.CONSTITUENT on CONSTITUENT.ID = @TARGETID
left join dbo.MODELINGANDPROPENSITY as CURRENTMODELINGANDPROPENSITY on CURRENTMODELINGANDPROPENSITY.ID = BATCHMODELINGANDPROPENSITYUPDATE.PRIMARYRECORDID
left join dbo.MODELINGANDPROPENSITY as SOURCEMODELINGANDPROPENSITY on SOURCEMODELINGANDPROPENSITY.ID = @SOURCEID
left join dbo.MODELINGANDPROPENSITY as TARGETMODELINGANDPROPENSITY on TARGETMODELINGANDPROPENSITY.ID = @TARGETID
where
BATCHMODELINGANDPROPENSITYUPDATE.PRIMARYRECORDID = @SOURCEID
or BATCHMODELINGANDPROPENSITYUPDATE.PRIMARYRECORDID = @TARGETID;
/* Built-in scores */
if exists (select 1 from dbo.MODELINGANDPROPENSITY where ID = @SOURCEID)
and not exists (select 1 from dbo.MODELINGANDPROPENSITY where ID = @TARGETID)
begin
-- When standard Blackbaud model scores exist on only the source record, retain this information on the merged, target record
-- Do an insert instead of an update to avoid foreign key issues
insert into dbo.MODELINGANDPROPENSITY
(
ID,
ANNUALGIFTLIKELIHOOD,
ANNUITYLIKELIHOOD,
BEQUESTLIKELIHOOD,
CRTLIKELIHOOD,
MAJORGIVINGLIKELIHOOD,
MEMBERSHIPLIKELIHOOD,
ONLINEGIVINGLIKELIHOOD,
PATIENTRESPONSELIKELIHOOD,
PLANNEDGIFTLIKELIHOOD,
SUGGESTEDMEMBERSHIPLEVELID,
NCOAMOVEDINDICATOR,
TARGETGIFTRANGEID,
WPSCREENINGINDICATOR,
WEALTHESTIMATORRATINGID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@TARGETID,
SOURCE.ANNUALGIFTLIKELIHOOD,
SOURCE.ANNUITYLIKELIHOOD,
SOURCE.BEQUESTLIKELIHOOD,
SOURCE.CRTLIKELIHOOD,
SOURCE.MAJORGIVINGLIKELIHOOD,
SOURCE.MEMBERSHIPLIKELIHOOD,
SOURCE.ONLINEGIVINGLIKELIHOOD,
SOURCE.PATIENTRESPONSELIKELIHOOD,
SOURCE.PLANNEDGIFTLIKELIHOOD,
SOURCE.SUGGESTEDMEMBERSHIPLEVELID,
SOURCE.NCOAMOVEDINDICATOR,
SOURCE.TARGETGIFTRANGEID,
SOURCE.WPSCREENINGINDICATOR,
SOURCE.WEALTHESTIMATORRATINGID,
@CHANGEAGENTID,
@CHANGEAGENTID,
SOURCE.DATEADDED,
SOURCE.DATECHANGED
from
dbo.MODELINGANDPROPENSITY as SOURCE
where
SOURCE.ID = @SOURCEID;
update
dbo.MODELINGANDPROPENSITYDELIVERY
set
MODELINGANDPROPENSITYID = @TARGETID
--no audit data
where
MODELINGANDPROPENSITYID = @SOURCEID;
end
else if exists (select 1 from dbo.MODELINGANDPROPENSITY where ID = @SOURCEID)
begin
-- When standard Blackbaud scores exist on both the source and the target records, retain all on the merged, target record as long as they are not duplicates of the same score
-- When scores for the same model exist on both the source and target records, keep only the Target record score.
update
dbo.MODELINGANDPROPENSITYDELIVERY
set
MODELINGANDPROPENSITYID = @TARGETID
where
MODELINGANDPROPENSITYID = @SOURCEID
and
-- Target has no delivery for that model
not exists
(
select
1
from
dbo.MODELINGANDPROPENSITYDELIVERY as TARGETDELIVERY
where
TARGETDELIVERY.MODELINGANDPROPENSITYID = @TARGETID
and
TARGETDELIVERY.ANALYTICSMODELID = MODELINGANDPROPENSITYDELIVERY.ANALYTICSMODELID
)
and
-- Target has no score for that model
(
select
case ANALYTICSMODELID
when '3CF4EC5A-63FE-42EA-B70B-7F6CAAE286F1' then case when ANNUALGIFTLIKELIHOOD <> -1 then 1 else 0 end
when '3788BA7E-E7DC-4A6E-93C9-F2245B26A13B' then case when ANNUITYLIKELIHOOD <> -1 then 1 else 0 end
when 'DEF85E09-E51A-4EED-B8AF-0763272A5593' then case when BEQUESTLIKELIHOOD <> -1 then 1 else 0 end
when 'FEB5BE0E-3B4F-45E1-A3E2-26F4372DEE96' then case when CRTLIKELIHOOD <> -1 then 1 else 0 end
when 'CE8C3F19-6B9A-4852-AB3C-3765F48578F7' then case when MAJORGIVINGLIKELIHOOD <> -1 then 1 else 0 end
when 'B2C12F11-3C63-4077-ADD0-C976A905B07C' then case when MEMBERSHIPLIKELIHOOD <> -1 then 1 else 0 end
when '06776DBB-E33A-45A2-B1DE-972B626169DC' then case when ONLINEGIVINGLIKELIHOOD <> -1 then 1 else 0 end
when '64111AFB-850D-4407-B403-948F0CD8BE25' then case when PATIENTRESPONSELIKELIHOOD <> -1 then 1 else 0 end
when '2018E9DB-F837-4CC0-BFC9-E72E5E63ACB5' then case when PLANNEDGIFTLIKELIHOOD <> -1 then 1 else 0 end
when 'A4BACAA3-8EE4-4476-B359-428E582C42AE' then case when SUGGESTEDMEMBERSHIPLEVELID is not null then 1 else 0 end
when 'CA0A6B61-2201-482F-AE8D-C6AE6852C318' then case when TARGETGIFTRANGEID is not null then 1 else 0 end
when '4D03C579-7461-42AE-8445-442D510B6151' then case when WEALTHESTIMATORRATINGID is not null then 1 else 0 end
else 0
end
from
dbo.MODELINGANDPROPENSITY
where
ID = @TARGETID
) = 0;
update
TARGET
set
TARGET.ANNUALGIFTLIKELIHOOD = case when TARGET.ANNUALGIFTLIKELIHOOD = -1 then SOURCE.ANNUALGIFTLIKELIHOOD else TARGET.ANNUALGIFTLIKELIHOOD end,
TARGET.ANNUITYLIKELIHOOD = case when TARGET.ANNUITYLIKELIHOOD = -1 then SOURCE.ANNUITYLIKELIHOOD else TARGET.ANNUITYLIKELIHOOD end,
TARGET.BEQUESTLIKELIHOOD = case when TARGET.BEQUESTLIKELIHOOD = -1 then SOURCE.BEQUESTLIKELIHOOD else TARGET.BEQUESTLIKELIHOOD end,
TARGET.CRTLIKELIHOOD = case when TARGET.CRTLIKELIHOOD = -1 then SOURCE.CRTLIKELIHOOD else TARGET.CRTLIKELIHOOD end,
TARGET.MAJORGIVINGLIKELIHOOD = case when TARGET.MAJORGIVINGLIKELIHOOD = -1 then SOURCE.MAJORGIVINGLIKELIHOOD else TARGET.MAJORGIVINGLIKELIHOOD end,
TARGET.MEMBERSHIPLIKELIHOOD = case when TARGET.MEMBERSHIPLIKELIHOOD = -1 then SOURCE.MEMBERSHIPLIKELIHOOD else TARGET.MEMBERSHIPLIKELIHOOD end,
TARGET.ONLINEGIVINGLIKELIHOOD = case when TARGET.ONLINEGIVINGLIKELIHOOD = -1 then SOURCE.ONLINEGIVINGLIKELIHOOD else TARGET.ONLINEGIVINGLIKELIHOOD end,
TARGET.PATIENTRESPONSELIKELIHOOD = case when TARGET.PATIENTRESPONSELIKELIHOOD = -1 then SOURCE.PATIENTRESPONSELIKELIHOOD else TARGET.PATIENTRESPONSELIKELIHOOD end,
TARGET.PLANNEDGIFTLIKELIHOOD = case when TARGET.PLANNEDGIFTLIKELIHOOD = -1 then SOURCE.PLANNEDGIFTLIKELIHOOD else TARGET.PLANNEDGIFTLIKELIHOOD end,
TARGET.SUGGESTEDMEMBERSHIPLEVELID = coalesce(TARGET.SUGGESTEDMEMBERSHIPLEVELID, SOURCE.SUGGESTEDMEMBERSHIPLEVELID),
--TARGET.NCOAMOVEDINDICATOR -- bit, no null
TARGET.TARGETGIFTRANGEID = coalesce(TARGET.TARGETGIFTRANGEID, SOURCE.TARGETGIFTRANGEID),
TARGET.WPSCREENINGINDICATOR = coalesce(nullif(TARGET.WPSCREENINGINDICATOR,''), SOURCE.WPSCREENINGINDICATOR),
TARGET.WEALTHESTIMATORRATINGID = coalesce(TARGET.WEALTHESTIMATORRATINGID, SOURCE.WEALTHESTIMATORRATINGID),
TARGET.CHANGEDBYID = @CHANGEAGENTID,
TARGET.DATECHANGED = case when @DATECHANGED is null or @DATECHANGED < TARGET.DATECHANGED then TARGET.DATECHANGED else @DATECHANGED end
from
dbo.MODELINGANDPROPENSITY as TARGET
left join dbo.MODELINGANDPROPENSITY as SOURCE on SOURCE.ID = @SOURCEID
where
TARGET.ID = @TARGETID;
exec dbo.USP_MODELINGANDPROPENSITY_DELETEBYID_WITHCHANGEAGENTID @SOURCEID, @CHANGEAGENTID;
end
/* Custom scores (attributes) */
-- When custom attribute ratings exist on only the source record, retain this information on the merged, target record
-- When custom attribute ratings exist on both the source and the target records for an attribute category that allows more than one value per record, retain all values on the merged, target record. Values are copied from the source to the target even if they create duplicate entries.
-- When scores for the same model exist on both the source and target records, keep only the Target record score.
exec dbo.USP_ATTRIBUTECATEGORY_MODELINGANDPROPENSITY_CONSTITUENTMERGE_AUTOGEN @SOURCEID, @TARGETID, @CHANGEAGENTID;
return 0;