Example modifications to the SQL upgrade script

In your existing Campaign environment, the CustomerID field in the UA_ContactHistory table has been changed to ID.

To accommodate this field name change, you would need to change all the occurrences of CustomerID to ID in the upgrade script.

Your existing Campaign environment contains an additional audience level called Household. To support this audience level, your database contains tables named HH_ContactHistory, HH_ResponseHistory, and HH_DtlContactHist. The primary key is HouseholdID.

To support the Household audience level in your new Campaign installation, locate the code in the SQL upgrade script that updates response history and treatment sizes for the Customer audience level and replicate it for your Household audience level. Change the table names in these statements to the appropriate names for your Household audience level, and change any references to CustomerID to HouseholdID.

The following example SQL statements show the required additions to the ac_upgrade_sqlsvr.sql script for a SQL Server database containing the Household audience level. Text that has been changed to support the Household audience level is in bold:

-- ResponseHistory update "template"
ALTER TABLE HH_ResponseHistory ADD DirectResponse int NULL
go

-- Update the treatment sizes

update ua_treatment
set treatmentsize=(select count(DISTINCT HouseholdID)
from HH_ContactHistory
where HH_ContactHistory.CellID = ua_treatment.CellID
AND HH_ContactHistory.PackageID = ua_treatment.PackageID
and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 0)

where exists

(select * from hh_contacthistory
where hh_contacthistory.CellID = ua_treatment.CellID
AND hh_contacthistory.PackageID = ua_treatment.PackageID
and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 0)
go

update ua_treatment
set treatmentsize=(select count(DISTINCT HouseholdID)
from HH_DtlContactHist
where HH_DtlContactHist.TreatmentInstID = ua_treatment.TreatmentInstID
and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 1)

where exists

(select * from hh_dtlcontacthist
where hh_dtlcontacthist.TreatmentInstID = ua_treatment.TreatmentInstID
and ua_treatment.CntrlTreatmtFlag = 1 and ua_treatment.HasDetailHistory = 1)
go