USP_MKTSEGMENTATIONSEGMENT_CACHEPREVIOUSSEGMENTEXCLUSIONS
Caches the donor and household exclusions for a marketing effort segment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEPREVIOUSSEGMENTEXCLUSIONS]
(
@SEGMENTID uniqueidentifier
)
as
set nocount on;
declare @SEGMENTATIONID uniqueidentifier;
declare @MAILINGTYPECODE tinyint;
declare @SEGMENTSEQUENCE int;
declare @HOUSEHOLDINGTYPECODE tinyint;
declare @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
declare @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
declare @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
declare @RECORDSOURCEID uniqueidentifier;
declare @RECORDSOURCETYPE tinyint;
declare @CONSTITIDDATATYPE nvarchar(50);
declare @CONSTITRANKVIEWNAME nvarchar(128);
declare @CONSTITRANKVALUECOLUMN nvarchar(128);
declare @CONSTITRANKORDERDESC bit;
declare @MKTSEGMENTID uniqueidentifier;
declare @RECORDTYPEID uniqueidentifier;
declare @CHANNELCODE tinyint;
declare @SAMPLESIZE int;
declare @SAMPLESIZETYPECODE tinyint;
declare @SAMPLESIZEMETHODCODE tinyint;
declare @SAMPLESIZEEXCLUDEREMAINDER bit;
declare @PREVSEGMENTVIEW nvarchar(128);
declare @PREVSEGMENTTEMPTABLE nvarchar(128);
declare @PREVRECORDS int;
declare @PREVOFFERS int;
declare @PREVUSERANDOM bit;
declare @PREVUSENTH bit;
declare @PREVUSETOP bit;
declare @PREVNTHRECORD decimal(30,20);
declare @CONSOLIDATEDVIEW nvarchar(128);
declare @SQL nvarchar(max);
declare @TEMPSQL nvarchar(max);
declare @SQLWITHJOINHINT nvarchar(max);
declare @PREVSEGMENTIDS nvarchar(max);
declare @EXCLUSIONSTABLENAME nvarchar(128);
declare @EXCLUSIONSTABLEIDFIELDNAME nvarchar(255);
declare @NEEDCAST bit = 0;
declare @TEMPNEEDCAST bit;
declare @CASTBEGIN nvarchar(15) = '###CASTBEGIN###';
declare @CASTEND nvarchar(13) = '###CASTEND###';
declare @CONSTITVIEWNAME nvarchar(128);
declare @CONSTITVIEWPRIMARYKEYFIELD nvarchar(255);
declare @CONSTITVIEWHOUSEHOLDIDFIELD nvarchar(255);
declare @CONSTITVIEWHEADOFHOUSEHOLDFIELD nvarchar(255);
declare @CONSTITVIEWISHOUSEHOLDFIELD nvarchar(255);
declare @HOUSEHOLDSTABLENAME nvarchar(128);
declare @HOUSEHOLDEXCLUSIONSTABLENAME nvarchar(128);
declare @HOUSEHOLDIDDATATYPE nvarchar(50);
declare @USEHOUSEHOLDING bit;
declare @USEHEADOFHOUSEHOLD bit;
declare @USEISHOUSEHOLD bit;
declare @USEADDRESSPROCESSING bit;
declare @USECONSTITUENTFILEIMPORTTABLE bit;
declare @ISBBEC bit;
declare @CONSOLIDATEDRECORDTYPES table ([RECORDTYPEID] uniqueidentifier primary key, [QUERYVIEWCATALOGID] uniqueidentifier not null);
declare @TEMPSEGMENTATIONSEGMENTTABLE nvarchar(128) = '#TEMP_SEGMENTATIONSEGMENT';
declare @MEMBERVIEWNAME nvarchar(128);
declare @MEMBERVIEWPRIMARYKEYFIELD nvarchar(128);
declare @LOCKPREFIX nvarchar(50) = 'SegmentExclusionCache:';
declare @LOCKERROR nvarchar(255) = 'A failure or timeout occurred while requesting an app lock to cache a marketing effort segment''s exclusions.';
declare @LOCKNAME nvarchar(255);
declare @LOCKRESULT int;
declare @RETVAL int = 0;
begin try
--Get an app lock for this segment so that we don't run into problems when this SP gets called more than once simultaneously.
set @LOCKNAME = @LOCKPREFIX + cast(@SEGMENTID as nvarchar(36));
exec @LOCKRESULT = sp_getapplock @Resource=@LOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=3600000;
if @LOCKRESULT < 0
raiserror(@LOCKERROR, 13, 1);
--Validate the required temp table exists...
if object_id('tempdb..' + @TEMPSEGMENTATIONSEGMENTTABLE) is null
raiserror('The temp table #TEMP_SEGMENTATIONSEGMENT is required to hold the results of this stored procedure but it does not exist. Please create the temp table before calling this stored procedure.', 13, 1);
--Grab some values from the segment...
select
@SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@SEGMENTSEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
@HOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
@HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
@RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
@RECORDSOURCETYPE = (case when [MKTSEGMENTATION].[MAILINGTYPECODE] in (2, 3, 5) then 1 else dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) end),
@CONSTITIDDATATYPE = [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME],
@MKTSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@PREVSEGMENTVIEW = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENT].[IDSETREGISTERID]),
@SAMPLESIZE = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
@SAMPLESIZETYPECODE = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
@SAMPLESIZEMETHODCODE = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE],
@SAMPLESIZEEXCLUDEREMAINDER = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
@RECORDTYPEID = dbo.[UFN_MKTSEGMENT_GETRECORDTYPE]([MKTSEGMENT].[ID]),
@USEADDRESSPROCESSING =
(case when
(
--Direct/appeal based mailings
--We intentionally do not want to include other mailing types or list segments (acknowledgments is handled in its own process and for membership/sponsorship we don't want to exclude people from the counts based on an invalid address or comm prefs).
([MKTSEGMENTATION].[MAILINGTYPECODE] = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] = 1)
or
--Altru acknowledgements/reminders - we have to call this out separately because they are not really "appeal" mailings like the rest
([MKTSEGMENTATION].[MAILINGTYPECODE] = 5 and [MKTSEGMENT].[SEGMENTTYPECODE] = 3)
)
and (case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] else [MKTSEGMENTATION].[USEADDRESSPROCESSING] end) = 1
and (case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end) is not null
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTSEGMENT].[QUERYVIEWCATALOGID]) = 1
and (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end) = 1
then 1 else 0 end),
@CHANNELCODE = isnull([MKTPACKAGE].[CHANNELCODE], 255),
@EXCLUSIONSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEXCLUSIONS_MAKETABLENAME]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
@USECONSTITUENTFILEIMPORTTABLE = (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] = 1 and exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME]([MKTSEGMENTATION].[ID])) then 1 else 0 end)
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
left join dbo.[APPEALMAILINGSETUP] on [MKTSEGMENTATION].[ID] = [APPEALMAILINGSETUP].[ID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
--print '@SEGMENTATIONID = ' + cast(@SEGMENTATIONID as nvarchar(36));
--print '@MAILINGTYPECODE = ' + cast(@MAILINGTYPECODE as nvarchar(1));
--print '@SEGMENTSEQUENCE = ' + cast(@SEGMENTSEQUENCE as nvarchar(10));
--print '@HOUSEHOLDINGTYPECODE = ' + cast(@HOUSEHOLDINGTYPECODE as nvarchar(10));
--print '@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = ' + cast(@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD as nvarchar(1));
--print '@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = ' + cast(@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS as nvarchar(1));
--print '@HOUSEHOLDINGONERECORDPERHOUSEHOLD = ' + cast(@HOUSEHOLDINGONERECORDPERHOUSEHOLD as nvarchar(1));
--print '@RECORDSOURCEID = ' + cast(@RECORDSOURCEID as nvarchar(36));
--print '@RECORDSOURCETYPE = ' + cast(@RECORDSOURCETYPE as nvarchar(10));
--print '@CONSTITIDDATATYPE = ' + @CONSTITIDDATATYPE;
--print '@USEADDRESSPROCESSING = ' + cast(@USEADDRESSPROCESSING as nvarchar(1));
if @MAILINGTYPECODE = 0 -- appeal
begin
if @RECORDSOURCETYPE = 3
begin
select
@CONSOLIDATEDVIEW = [QUERYVIEWCATALOG].[OBJECTNAME]
from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
where [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = @RECORDSOURCEID;
insert into @CONSOLIDATEDRECORDTYPES ([RECORDTYPEID], [QUERYVIEWCATALOGID])
--House file record type...
select [RECORDTYPEID], [ID]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @RECORDSOURCEID
union
--List record types...
select [MKTSEGMENTLIST].[IDSETRECORDTYPEID], [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
where [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = @RECORDSOURCEID;
--Figure out if the previous segment will force us to cast the IDs to varchar(36) or not. If we can get
--away with NOT casting, this will execute faster because it can use the indexes instead of doing table
--scans. We only need to cast when we use a consolidated view and the view's primary key is NOT a guid.
select @NEEDCAST = (case when [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME] = 'uniqueidentifier' then 0 else 1 end)
from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
where [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = @RECORDSOURCEID;
end
end
else if @MAILINGTYPECODE = 2 -- membership
begin
select
@MEMBERVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@MEMBERVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@EXCLUSIONSTABLEIDFIELDNAME = 'MEMBERSHIPID'
from dbo.[MKTMEMBERSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTMEMBERSHIPRECORDSOURCE].[ID] = @RECORDSOURCEID;
end
else if @MAILINGTYPECODE = 3 -- sponsorship
set @EXCLUSIONSTABLEIDFIELDNAME = 'SPONSORSHIPID';
else if @MAILINGTYPECODE = 5 -- communication revenue
set @EXCLUSIONSTABLEIDFIELDNAME = 'REVENUEID';
--Grab some constituent field mappings...
select
@CONSTITVIEWNAME = (case when [QUERYVIEWCATALOG].[OBJECTNAME] = 'V_QUERY_CONSTITUENTMARKETING' then 'V_QUERY_CONSTITUENTMARKETING_OPTIMIZED' else [QUERYVIEWCATALOG].[OBJECTNAME] end),
@CONSTITVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@CONSTITVIEWHOUSEHOLDIDFIELD = [MKTRECORDSOURCEFIELDMAPPINGS].[HOUSEHOLDIDFIELD],
@CONSTITVIEWHEADOFHOUSEHOLDFIELD = [MKTRECORDSOURCEFIELDMAPPINGS].[HEADOFHOUSEHOLDFIELD],
@CONSTITVIEWISHOUSEHOLDFIELD = [MKTRECORDSOURCEFIELDMAPPINGS].[ISHOUSEHOLDFIELD],
@USEHOUSEHOLDING = (case when @MAILINGTYPECODE = 0 and @RECORDSOURCETYPE <> 2 and @HOUSEHOLDINGTYPECODE <> 0 then 1 else 0 end),
@USEHEADOFHOUSEHOLD = (case when isnull([MKTRECORDSOURCEFIELDMAPPINGS].[HEADOFHOUSEHOLDFIELD],'') = '' then 0 else 1 end),
@USEISHOUSEHOLD = (case when isnull([MKTRECORDSOURCEFIELDMAPPINGS].[ISHOUSEHOLDFIELD],'') = '' then 0 else 1 end),
@ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTRECORDSOURCE].[ID])
from dbo.[MKTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
left join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTRECORDSOURCE].[ID]
where [MKTRECORDSOURCE].[ID] = @RECORDSOURCEID
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
--Locate the ranking data in case a segment uses top (@PREVUSETOP)
--TODO: TommyVe 6/15/2009 We want to eventually extend this to not depend directly on a segmentation generator for the ranking data view
-- AAW 11/19/09: Removing references to segmented appeal mailings/segmentation generator. Leaving this code around in case DM
-- finds it useful.
--select top 1
-- @CONSTITRANKVIEWNAME = ('SMARTFIELD' + replace(cast([SMARTFIELD].[TABLECATALOGID] as nvarchar(36)), '-', '')),
-- @CONSTITRANKVALUECOLUMN = [SMARTFIELD].[VALUECOLUMNNAME],
-- @CONSTITRANKORDERDESC = 1 --Always order by desc for smart field values
--from dbo.[SEGMENTEDAPPEALMAILING]
--inner join dbo.[SEGMENTATIONGENERATORDIMENSION] on [SEGMENTEDAPPEALMAILING].[SEGMENTATIONGENERATORID] = [SEGMENTATIONGENERATORDIMENSION].[SEGMENTATIONGENERATORID]
--inner join dbo.[SMARTFIELD] on [SEGMENTATIONGENERATORDIMENSION].[SMARTFIELDID] = [SMARTFIELD].[ID]
--inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELD].[SMARTFIELDCATALOGID] = [SMARTFIELDCATALOG].[ID]
--where [SEGMENTEDAPPEALMAILING].[ID] = @SEGMENTATIONID
--order by [SEGMENTATIONGENERATORDIMENSION].[SEQUENCE];
set @PREVUSETOP = (case when @SAMPLESIZEEXCLUDEREMAINDER = 0 and ((@SAMPLESIZETYPECODE = 0 and @SAMPLESIZE <> 100) or (@SAMPLESIZETYPECODE = 1)) and @SAMPLESIZEMETHODCODE = 2 and @CONSTITRANKVIEWNAME is not null then 1 else 0 end);
set @PREVUSERANDOM = (case when @SAMPLESIZEEXCLUDEREMAINDER = 0 and ((@SAMPLESIZETYPECODE = 0 and @SAMPLESIZE <> 100) or (@SAMPLESIZETYPECODE = 1)) and @SAMPLESIZEMETHODCODE = 1 then 1 else 0 end);
set @PREVUSENTH = (case when @SAMPLESIZEEXCLUDEREMAINDER = 0 and ((@SAMPLESIZETYPECODE = 0 and @SAMPLESIZE <> 100) or (@SAMPLESIZETYPECODE = 1)) and @SAMPLESIZEMETHODCODE = 0 then 1 else 0 end);
if @USEHOUSEHOLDING = 1
begin
--If HouseholdingType = Qualifying individuals, and the installed product is BBDM/RE7....
--and if NOT sending to one person per household, then do NOT use housholding because for RE7 the household
--record is the head-of-household (HoH) person, so the counts will come out incorrect when processing qualified
--individuals because it will exclude all the HoH's because it thinks they are household records.
if @HOUSEHOLDINGTYPECODE = 1 and @ISBBEC = 0
begin
if @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 0
set @USEHOUSEHOLDING = 0;
set @USEISHOUSEHOLD = 0;
end
if @USEHOUSEHOLDING = 1
begin
--Raise an error if the household fields have not been mapped for this record source...
if isnull(@CONSTITVIEWHOUSEHOLDIDFIELD, '') = '' or isnull(@CONSTITVIEWHEADOFHOUSEHOLDFIELD, '') = '' or isnull(@CONSTITVIEWISHOUSEHOLDFIELD, '') = ''
begin
declare @RECORDSOURCENAME nvarchar(255);
declare @RECORDSOURCEERROR nvarchar(1000);
select
@RECORDSOURCENAME = [QUERYVIEWCATALOG].[DISPLAYNAME]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @RECORDSOURCEID;
set @RECORDSOURCEERROR = 'The ''Household ID'', ''Head of household'', or ''Is household'' field(s) are not mapped for the ''' + @RECORDSOURCENAME + ''' record source.';
raiserror(@RECORDSOURCEERROR, 13, 1);
end
--Get the data type for the HouseholdID column...
select
@HOUSEHOLDIDDATATYPE = (case when lower([DATA_TYPE]) in ('nvarchar', 'varchar', 'char') then [DATA_TYPE] + '(' + cast([CHARACTER_MAXIMUM_LENGTH] as nvarchar(10)) + ')' else [DATA_TYPE] end)
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_SCHEMA] = 'dbo'
and [TABLE_NAME] = @CONSTITVIEWNAME
and [COLUMN_NAME] = @CONSTITVIEWHOUSEHOLDIDFIELD;
if @HOUSEHOLDIDDATATYPE <> @CONSTITIDDATATYPE
raiserror('The ''Household ID'' data type must be the same as the ''Constituent ID'' data type.', 13, 1);
set @HOUSEHOLDSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEHOUSEHOLDS_MAKETABLENAME](@RECORDSOURCEID);
set @HOUSEHOLDEXCLUSIONSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEHOUSEHOLDEXCLUSIONS_MAKETABLENAME](@RECORDSOURCEID);
end
end
--print '@EXCLUSIONSTABLENAME = ' + @EXCLUSIONSTABLENAME;
--print '@USEHOUSEHOLDING = ' + cast(@USEHOUSEHOLDING as nvarchar(1));
--print '@USEHEADOFHOUSEHOLD = ' + cast(@USEHEADOFHOUSEHOLD as nvarchar(1));
--print '@USEISHOUSEHOLD = ' + cast(@USEISHOUSEHOLD as nvarchar(1));
--print '';
select @PREVSEGMENTIDS = isnull(stuff((
select ', ''' + cast([MKTSEGMENTATIONSEGMENT].[ID] as nvarchar(36)) + ''''
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and ([MKTSEGMENT].[SEGMENTTYPECODE] in (1, 3, 4, 5) or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] <> 1))
and [MKTSEGMENTATIONSEGMENT].[SEQUENCE] < @SEGMENTSEQUENCE
order by [MKTSEGMENTATIONSEGMENT].[ID]
for xml path('')), 1, 2, ''), '''00000000-0000-0000-0000-000000000000''');
--Get an app lock for this segment so that other users cannot change it while we are using it...
exec @LOCKRESULT = dbo.[USP_MKTSEGMENT_GETAPPLOCK] @MKTSEGMENTID, 0;
if @LOCKRESULT < 0
raiserror(@LOCKERROR, 13, 1);
/***********************************/
/* Populate the segment temp table */
/***********************************/
--Put the segment's record IDs into a temp table (this takes the place of the old segment views)...
declare @USEBASETEMPTABLE bit = (case when @USEHOUSEHOLDING = 1 or (@USEADDRESSPROCESSING = 1 and (@CHANNELCODE = 0 or @CHANNELCODE = 1 or @MAILINGTYPECODE = 5)) then 1 else 0 end);
declare @BASESEGMENTTEMPTABLE nvarchar(128);
declare @GLOBALTEMPSEGMENTCONSTITUENTTABLE nvarchar(128) = '##TEMP_SEGMENT_CONSTITUENT_' + replace(cast(@SEGMENTID as nvarchar(36)), '-', '_');
set @TEMPSQL = null;
set @SQL = 'insert into ';
--Create a temp table to hold the base segment IDs. We have to use the base segment because the exclusions
--report relies on this information to figure out address processing exclusions. It also improves the
--householding performance by only using the segment view this one time.
if @USEBASETEMPTABLE = 1
begin
--Because this temp table needs to work with both BBEC and RE7 data, we need to create it dynamically (which means it has to be global) with the correct data type...
set @SQL = 'if object_id(''tempdb..' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ''') is not null' + char(13) +
' drop table ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ';' + char(13) +
char(13) +
'create table ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' ([ID] ' + (case when @NEEDCAST = 1 then 'varchar(36)' else @CONSTITIDDATATYPE end) + ' primary key);' + char(13) +
char(13) +
@SQL;
if @MAILINGTYPECODE = 5
begin
create table #TEMP_SEGMENT_OTHER ([ID] uniqueidentifier primary key);
set @BASESEGMENTTEMPTABLE = '#TEMP_SEGMENT_OTHER';
end
else
set @BASESEGMENTTEMPTABLE = @GLOBALTEMPSEGMENTCONSTITUENTTABLE;
set @SQL += @BASESEGMENTTEMPTABLE;
end
else
set @SQL += @TEMPSEGMENTATIONSEGMENTTABLE;
set @SQL += ' ([ID])' + char(13) +
' select distinct [SEG].[ID]' + char(13);
if @USECONSTITUENTFILEIMPORTTABLE = 1
--If there is a segmented house file imported for this mailing, then every segment in the mailing should
--look in the import table for its constituent IDs.
set @SQL += ' from (select distinct [RECORDID] as [ID] from dbo.[' + dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID) + '] where [SEGMENTATIONSEGMENTID] = ''' + convert(nvarchar(36), @SEGMENTID) + ''') as [SEG]' + char(13);
else
set @SQL += ' from dbo.' + @PREVSEGMENTVIEW + ' as [SEG]' + char(13);
--This logic is here for the exclusions report. In order to correctly determine who is excluded because of address processing
-- we need to run address processing on every constituent in the segment. This logic is here to keep us from selecting
-- from the original segment view twice. This way we select from the segment view once, then copy the contents of that temp
-- table to our original temp table.
if @USEBASETEMPTABLE = 1
begin
-- Copy original segment view to the @BASESEGMENTTEMPTABLE temp table.
--print '--Copy original segment view IDs to temp table for use in address processing...';
--print @SQL;
exec (@SQL);
if @MAILINGTYPECODE = 5
begin
--print '--Translate segment IDs to constituent IDs to be used in address processing...';
set @SQL = 'insert into ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' ([ID])' + char(13) +
' select distinct [FINANCIALTRANSACTION].[CONSTITUENTID]' + char(13) +
' from #TEMP_SEGMENT_OTHER as [SEG]' + char(13) +
' inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [SEG].[ID]';
--print @SQL + char(13);
exec sp_executesql @SQL;
end
-- Build original SQL, now selecting from the @BASESEGMENTTEMPTABLE temp table.
set @SQL = 'insert into ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' ([ID])' + char(13) +
' select distinct [SEG].[ID]' + char(13) +
' from ' + @BASESEGMENTTEMPTABLE + ' as [SEG]' + char(13);
end
--Universe
if @RECORDSOURCETYPE = 3 --Consolidated
begin
declare @VALIDRECORDTYPES table ([RECORDTYPEID] uniqueidentifier primary key);
declare @RECORDTYPEIDS nvarchar(max);
insert into @VALIDRECORDTYPES ([RECORDTYPEID])
select [RECORDTYPEID]
from @CONSOLIDATEDRECORDTYPES
where object_id('tempdb..#TEMP_UNIVERSE_' + replace(cast([RECORDTYPEID] as nvarchar(36)), '-', '_')) is not null;
if exists(select * from @VALIDRECORDTYPES)
begin
set @SQL += ' inner join (' + char(13) +
stuff(replace((
select ' union' + char(13) + ' select ' + @CASTBEGIN + '[ID]' + @CASTEND + ' as [ID] from dbo.[#TEMP_UNIVERSE_' + replace(cast([RECORDTYPEID] as nvarchar(36)), '-', '_') + ']' + char(13)
from @VALIDRECORDTYPES
for xml path('')
), '
', char(13)), 1, 10, '');
set @RECORDTYPEIDS = stuff((
select ', ''' + cast([QUERYVIEWCATALOGID] as nvarchar(36)) + ''''
from @CONSOLIDATEDRECORDTYPES
where [RECORDTYPEID] in (select [RECORDTYPEID] from @VALIDRECORDTYPES)
for xml path('')
), 1, 2, '');
--Since this is a consolidated segment, we need to make sure that a universe selection of one record type does not exclude IDs
--in this segment from another record type, so we need to add back the IDs from record types that are not represented by universe
--selections so that we only exclude the IDs of the same record types.
if isnull(@RECORDTYPEIDS, '') <> ''
set @SQL += ' union' + char(13) +
' select [ID] from dbo.[' + @CONSOLIDATEDVIEW + '] where [SOURCEQUERYVIEWID] not in (' + @RECORDTYPEIDS + ')' + char(13);
set @SQL += ' ) as [UNIVERSE] on [UNIVERSE].[ID] = [SEG].[ID]' + char(13);
end
end
else
begin
declare @TEMPUNIVERSETABLE nvarchar(128) = '#TEMP_UNIVERSE_' + replace(cast(@RECORDTYPEID as nvarchar(36)), '-', '_');
if object_id('tempdb..' + @TEMPUNIVERSETABLE) is not null
set @SQL += ' inner join dbo.[' + @TEMPUNIVERSETABLE + '] as [UNIVERSE] on [UNIVERSE].[ID] = [SEG].[ID]' + char(13);
end
--Mailing exclusions
if @RECORDSOURCETYPE = 3 --Consolidated
begin
if exists(select * from @CONSOLIDATEDRECORDTYPES where object_id('tempdb..#TEMP_EXCLUSIONS_' + replace(cast([RECORDTYPEID] as nvarchar(36)), '-', '_')) is not null)
begin
set @SQL += ' left join (' + char(13) +
stuff(replace((
select ' union' + char(13) + ' select ' + @CASTBEGIN + '[ID]' + @CASTEND + ' as [ID] from dbo.[#TEMP_EXCLUSIONS_' + replace(cast([RECORDTYPEID] as nvarchar(36)), '-', '_') + ']' + char(13)
from @CONSOLIDATEDRECORDTYPES
where object_id('tempdb..#TEMP_EXCLUSIONS_' + replace(cast([RECORDTYPEID] as nvarchar(36)), '-', '_')) is not null
for xml path('')
), '
', char(13)), 1, 10, '') +
' ) as [EXCLUSIONS] on [EXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
set @TEMPSQL = isnull(@TEMPSQL + ' and', ' where') + ' [EXCLUSIONS].[ID] is null' + char(13);
end
end
else
begin
declare @TEMPEXCLUSIONSTABLE nvarchar(128) = '#TEMP_EXCLUSIONS_' + replace(cast(@RECORDTYPEID as nvarchar(36)), '-', '_');
if object_id('tempdb..' + @TEMPEXCLUSIONSTABLE) is not null
begin
set @SQL += ' left join (select distinct [ID] from dbo.[' + @TEMPEXCLUSIONSTABLE + ']) as [EXCLUSIONS] on [EXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
set @TEMPSQL = isnull(@TEMPSQL + ' and', ' where') + ' [EXCLUSIONS].[ID] is null' + char(13);
end
end
--Previous segment exclusions
if @SEGMENTSEQUENCE > 1
begin
if @MAILINGTYPECODE = 0 --appeal
begin
if @RECORDSOURCETYPE = 1 --record source
set @SQL += ' left join (select [DONORID] as [ID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [DONORID] is not null' + char(13) +
' and [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' ) as [PREVIOUSSEGMENTEXCLUSIONS] on [PREVIOUSSEGMENTEXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
else if @RECORDSOURCETYPE = 2 --list
set @SQL += ' left join (select [LISTDONORID] as [ID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [LISTDONORID] is not null' + char(13) +
' and [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' ) as [PREVIOUSSEGMENTEXCLUSIONS] on [PREVIOUSSEGMENTEXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
else if @RECORDSOURCETYPE = 3 --consolidated list
set @SQL += ' left join (select isnull(' + @CASTBEGIN + '[DONORID]' + @CASTEND + ', ' + @CASTBEGIN + '[LISTDONORID]' + @CASTEND + ') as [ID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' ) as [PREVIOUSSEGMENTEXCLUSIONS] on [PREVIOUSSEGMENTEXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
end
else
begin
set @SQL += ' left join (select [' + @EXCLUSIONSTABLEIDFIELDNAME + '] as [ID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' ) as [PREVIOUSSEGMENTEXCLUSIONS] on [PREVIOUSSEGMENTEXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
end
set @TEMPSQL = isnull(@TEMPSQL + ' and', ' where') + ' [PREVIOUSSEGMENTEXCLUSIONS].[ID] is null' + char(13);
end
--Householding exclusions
if @USEHOUSEHOLDING = 1
begin
set @SQL += ' left join (select ' + @CASTBEGIN + '[DONORID]' + @CASTEND + ' as [ID]' + char(13) +
' from dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ', ''' + cast(@SEGMENTID as nvarchar(36)) + ''')' + char(13) +
' ) as [HOUSEHOLDINGEXCLUSIONS] on [HOUSEHOLDINGEXCLUSIONS].[ID] = [SEG].[ID]' + char(13);
set @TEMPSQL = isnull(@TEMPSQL + ' and', ' where') + ' [HOUSEHOLDINGEXCLUSIONS].[ID] is null' + char(13);
end
--Deleted record exclusions - these joins will filter out any deleted records
if @MAILINGTYPECODE = 0
begin
if @RECORDSOURCETYPE = 1 --record source
begin
if @ISBBEC = 1
set @SQL += ' inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [SEG].[ID]' + char(13);
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] = [SEG].[ID]' + char(13);
end
else if @RECORDSOURCETYPE = 3 --consolidated list
set @SQL += ' inner join dbo.[' + @CONSOLIDATEDVIEW + '] as [CONSOL] on [CONSOL].[ID] = [SEG].[ID]' + char(13);
end
else if @MAILINGTYPECODE = 2 --membership
begin
if @ISBBEC = 1
set @SQL += ' inner join dbo.[MEMBER] on [MEMBER].[MEMBERSHIPID] = [SEG].[ID] and [MEMBER].[ISPRIMARY] = 1 and [MEMBER].[ISDROPPED] = 0' + char(13) +
' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID] and [MEMBERSHIP].[STATUSCODE] <> 1' + char(13);
else
set @SQL += ' inner join dbo.[' + @MEMBERVIEWNAME + '] as [MEMBERS] on [MEMBERS].[' + @MEMBERVIEWPRIMARYKEYFIELD + '] = [SEG].[ID] and [MEMBERS].[ISPRIMARY] = 1 and [MEMBERS].[ISDROPPED] = 0' + char(13);
end
else if @MAILINGTYPECODE = 3 --sponsorship
begin
--Sponsorship exclusions
set @SQL += ' inner join dbo.[SPONSORSHIP] on [SPONSORSHIP].[ID] = [SEG].[ID]' + char(13) +
' left join dbo.[MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED] on [MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED].[MKTSEGMENTATIONID] = ''' + cast(@SEGMENTATIONID as nvarchar(36)) + ''' and [MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED].[SPONSORSHIPID] = [SPONSORSHIP].[ID]' + char(13) +
' left join dbo.[SPONSORSHIPTRANSACTION] as [LASTTRANSACTION] on [LASTTRANSACTION].[SPONSORSHIPCOMMITMENTID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID] and [LASTTRANSACTION].[TRANSACTIONSEQUENCE] = (select max([TRANSACTIONSEQUENCE]) from dbo.[SPONSORSHIPTRANSACTION] as [MAXSEQUENCE] where [MAXSEQUENCE].[SPONSORSHIPCOMMITMENTID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID] and [SPONSORSHIP].[ID] in ([MAXSEQUENCE].[CONTEXTSPONSORSHIPID], [MAXSEQUENCE].[TARGETSPONSORSHIPID], [MAXSEQUENCE].[DECLINEDSPONSORSHIPID]))' + char(13);
set @TEMPSQL = isnull(@TEMPSQL + ' and', ' where') + ' ([MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED].[ID] is null or [MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED].[DATESENT] < [LASTTRANSACTION].[DATECHANGED])' + char(13);
end
else if @MAILINGTYPECODE = 5 --communication revenue
begin
set @SQL += ' inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [SEG].[ID] and [FINANCIALTRANSACTION].[DELETEDON] is null' + char(13);
end
set @SQL += isnull(@TEMPSQL, '');
if @NEEDCAST = 1
begin
set @SQL = replace(@SQL, @CASTBEGIN, 'cast(');
set @SQL = replace(@SQL, @CASTEND, ' as varchar(36))');
end
else
begin
set @SQL = replace(@SQL, @CASTBEGIN, '');
set @SQL = replace(@SQL, @CASTEND, '');
end
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
if @NEEDCAST = 1
set @SQLWITHJOINHINT = @SQL + ' option (hash join, merge join)';
else
set @SQLWITHJOINHINT = @SQL;
--print '--Populate mailing segment temp table...';
--print @SQLWITHJOINHINT + char(13) + char(13);
exec (@SQLWITHJOINHINT);
end try
begin catch
if ERROR_NUMBER() = 8622
begin
--print '--Above join hint failed, retrying SQL without join hint...' + char(13) + char(13);
exec (@SQL);
end
else
begin
exec dbo.[USP_RAISE_ERROR];
raiserror('Populating the segment temp table failed.', 13, 1);
end
end catch
--Cache the addresses for this constituent segment (BBEC direct/appeal mailings only, do not include acknowledgement, membership, or sponsorship mailings)...
if @USEADDRESSPROCESSING = 1
begin
--print '-------------------------------------------------------------------------------------------------------------------------';
if @CHANNELCODE = 0
begin
--print '--Address processing...';
--print 'exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEADDRESSES] @SEGMENTID;' + char(13);
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEADDRESSES] @SEGMENTID;
--print '--Remove records that do not have a valid address...';
set @SQL = 'delete from ' + @TEMPSEGMENTATIONSEGMENTTABLE + char(13);
if @MAILINGTYPECODE = 0 and @RECORDSOURCETYPE = 3 -- consolidated list: only constituents are subject to address processing
set @SQL += 'from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [TEMP]' + char(13) +
'inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [TEMP].[ID]' + char(13) +
'where [TEMP].[ID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '] where [SEGMENTID] = @SEGMENTID)';
else if @MAILINGTYPECODE = 5 --communication revenue
set @SQL += 'from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [TEMP]' + char(13) +
'inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [TEMP].[ID]' + char(13) +
'where [FINANCIALTRANSACTION].[CONSTITUENTID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '] where [SEGMENTID] = @SEGMENTID)';
else
set @SQL += 'where [ID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '] where [SEGMENTID] = @SEGMENTID)';
--print @SQL + char(13) + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
end
else if @CHANNELCODE = 1
begin
--print '--Email address processing...';
--print 'exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEEMAILADDRESSES] @SEGMENTID;' + char(13);
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEEMAILADDRESSES] @SEGMENTID;
--This is only dynamic sql so that this USP will compile and work when the temp table datatype is different than uniqueidentifier (ie - RE7)...
--print '--Remove records that do not have a valid email address...';
set @SQL = 'delete from ' + @TEMPSEGMENTATIONSEGMENTTABLE + char(13);
if @MAILINGTYPECODE = 0 and @RECORDSOURCETYPE = 3 -- consolidated list: only constituents are subject to address processing
set @SQL += 'from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [TEMP]' + char(13) +
'inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [TEMP].[ID]' + char(13) +
'where [TEMP].[ID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '])';
else if @MAILINGTYPECODE = 5 --communication revenue
set @SQL += 'from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [TEMP]' + char(13) +
'inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [TEMP].[ID]' + char(13) +
'where [FINANCIALTRANSACTION].[CONSTITUENTID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '])';
else
set @SQL += 'where [ID] not in (select [CONSTITUENTID] from dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID) + '])';
--print @SQL + char(13) + char(13);
exec sp_executesql @SQL;
end
end
--Drop the temp table...
if object_id('tempdb..#TEMP_SEGMENT_OTHER') is not null
drop table #TEMP_SEGMENT_OTHER;
/***************************************/
/* End populate the segment temp table */
/***************************************/
/*****************************************************/
/* Current and previous segment household exclusions */
/*****************************************************/
if @USEHOUSEHOLDING = 1
begin
--print '-------------------------------------------------------------------------------------------------------------------------';
--print '--Household Previous Segment: ' + cast(@SEGMENTSEQUENCE as nvarchar(10)) + ', ' + cast(@SEGMENTID as nvarchar(36)) + ', ' + @PREVSEGMENTVIEW;
--print '-------------------------------------------------------------------------------------------------------------------------';
--Check if we need to process household duplicates within the current segment...
if @HOUSEHOLDINGTYPECODE = 1 and @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 1 --Qualifying individuals and one record per household
begin
/****************************************/
/* Current segment household exclusions */
/****************************************/
--print '-------------------------------------------------------------------------------------------------------------------------';
--print '-- Processing duplicate households in segment...';
--print '-------------------------------------------------------------------------------------------------------------------------';
--Exclude people from the same household for the current segment...
set @SQL = 'declare @DUPEHHIDS table ([HHID] ' + @HOUSEHOLDIDDATATYPE + ' primary key, [DONORID] ' + @CONSTITIDDATATYPE + ' not null);' + char(13) +
'declare @HHIDS table ([HOUSEHOLDID] ' + @HOUSEHOLDIDDATATYPE + ' primary key);' + char(13) +
char(13) +
'--Grab all the households that appear in this segment more than once...' + char(13) +
'insert into @HHIDS ([HOUSEHOLDID])' + char(13);
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' select [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]' + char(13) +
' from ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' as [SEG]' + char(13) +
' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' where [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] is not null' + char(13) +
' group by [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]' + char(13);
else
set @SQL += ' select [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' + char(13) +
' from ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' as [SEG]' + char(13) +
' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' where [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] is not null' + char(13) +
' group by [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' + char(13);
set @SQL += ' having count(*) > 1;' + char(13) +
char(13) +
'--For each household above, grab the ' + (case when @PREVUSETOP = 1 then 'top person (according to the ranking view)...' when @USEHEADOFHOUSEHOLD = 1 then '"Head of Household" if one exists, otherwise just use the ' else '' end) + 'first person found...' + char(13) +
'insert into @DUPEHHIDS ([HHID], [DONORID])' + char(13) +
' select [HOUSEHOLDID], [CONSTITUENTID]' + char(13) +
' from (' + char(13);
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' select [HHIDS].[HOUSEHOLDID], [CONSTITUENTHOUSEHOLD].[ID] as [CONSTITUENTID], row_number() over (partition by [HHIDS].[HOUSEHOLDID] order by ' + (case when @PREVUSETOP = 1 then '[CONSTITSRANK].[' + @CONSTITRANKVALUECOLUMN + ']' + (case when @CONSTITRANKORDERDESC = 1 then ' desc' else '' end) when @USEHEADOFHOUSEHOLD = 1 then '[CONSTITUENTHOUSEHOLD].[ISPRIMARYMEMBER] desc' else 'newid()' end) + ') as [RANK]' + char(13) +
' from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [SEG]' + char(13) +
' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' inner join @HHIDS as [HHIDS] on [HHIDS].[HOUSEHOLDID] = [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]' + char(13) +
(case when @PREVUSETOP = 1 then ' left join [' + @CONSTITRANKVIEWNAME + '] as [CONSTITSRANK] on [CONSTITSRANK].[ID] = [CONSTITUENTHOUSEHOLD].[ID]' + char(13) else '' end);
else
set @SQL += ' select [HHIDS].[HOUSEHOLDID], [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] as [CONSTITUENTID], row_number() over (partition by [HHIDS].[HOUSEHOLDID] order by ' + (case when @PREVUSETOP = 1 then '[CONSTITSRANK].[' + @CONSTITRANKVALUECOLUMN + ']' + (case when @CONSTITRANKORDERDESC = 1 then ' desc' else '' end) when @USEHEADOFHOUSEHOLD = 1 then '[CONSTITS].[' + @CONSTITVIEWHEADOFHOUSEHOLDFIELD + '] desc' else 'newid()' end) + ') as [RANK]' + char(13) +
' from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [SEG]' + char(13) +
' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' inner join @HHIDS as [HHIDS] on [HHIDS].[HOUSEHOLDID] = [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' + char(13) +
(case when @PREVUSETOP = 1 then ' left join [' + @CONSTITRANKVIEWNAME + '] as [CONSTITSRANK] on [CONSTITSRANK].[ID] = [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + char(13) else '' end);
set @SQL += ' ) as [T]' + char(13) +
' where [RANK] = 1;' + char(13) +
char(13) +
'if exists(select top 1 1 from @DUPEHHIDS)' + char(13) +
' begin' + char(13) +
' --Exclude all other people, except the ' + (case when @PREVUSETOP = 1 then 'top-ranked person' when @USEHEADOFHOUSEHOLD = 1 then '"Head of Household" (or first person)' else 'first person' end) + ' we found above, from the households that appear in this segment more than once...' + char(13);
if @SEGMENTSEQUENCE > 1
set @SQL = @SQL +
' declare @TEMP table([DONORID] ' + (case when @NEEDCAST = 1 then 'varchar(36)' else @CONSTITIDDATATYPE end) + ' primary key);' + char(13) +
char(13) +
' insert into @TEMP ([DONORID])' + char(13) +
' select [DONORID]' + char(13) +
' from dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ');' + char(13) +
char(13);
set @SQL = @SQL +
' insert into dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + '] ([SEGMENTID], [DONORID], [DUPLICATE])' + char(13) +
' select @SEGMENTID, ' + (case when @ISBBEC = 1 then '[CONSTITUENTHOUSEHOLD].[ID]' else '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' end) + ', 1' + char(13) +
' from ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' as [SEG]' + char(13);
if @RECORDSOURCETYPE = 3
set @SQL = @SQL + ' inner join dbo.[' + @CONSOLIDATEDVIEW + '] as [CONSOL] on [CONSOL].[SOURCEQUERYVIEWID] = @RECORDSOURCEID and [CONSOL].[ID] = [SEG].[ID]' + char(13);
--Since we are processing duplicate households within a single segment, and since we always pick the head of household
--when there are duplicates, that means the head of household can never be excluded here because of householding so make
--sure only non-heads get put into the household exclusions table so the exclusion report will show correctly.
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID] and [CONSTITUENTHOUSEHOLD].[ISPRIMARYMEMBER] = 0' + char(13) +
' inner join @DUPEHHIDS as [DUPEHHIDS] on [DUPEHHIDS].[HHID] = [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] and ' + @CASTBEGIN + '[DUPEHHIDS].[DONORID]' + @CASTEND + ' <> [SEG].[ID]';
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID] and [CONSTITS].[' + @CONSTITVIEWHEADOFHOUSEHOLDFIELD + '] = 0' + char(13) +
' inner join @DUPEHHIDS as [DUPEHHIDS] on [DUPEHHIDS].[HHID] = [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] and ' + @CASTBEGIN + '[DUPEHHIDS].[DONORID]' + @CASTEND + ' <> [SEG].[ID]';
if @SEGMENTSEQUENCE > 1
set @SQL = @SQL + char(13) +
' where (not exists(select top 1 1 from @TEMP) or [SEG].[ID] not in (select [DONORID] from @TEMP))';
set @SQL = @SQL + '###JOINHINT###;' + char(13) + ' end';
if @NEEDCAST = 1
begin
set @SQL = replace(@SQL, @CASTBEGIN, 'cast(');
set @SQL = replace(@SQL, @CASTEND, ' as varchar(36))');
end
else
begin
set @SQL = replace(@SQL, @CASTBEGIN, '');
set @SQL = replace(@SQL, @CASTEND, '');
end
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
if @NEEDCAST = 1
set @SQLWITHJOINHINT = replace(@SQL, '###JOINHINT###', char(13) + ' option (hash join, merge join)');
else
set @SQLWITHJOINHINT = replace(@SQL, '###JOINHINT###', '');
--print @SQLWITHJOINHINT + char(13) + char(13);
exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @RECORDSOURCEID uniqueidentifier', @SEGMENTID = @SEGMENTID, @RECORDSOURCEID = @RECORDSOURCEID;
end try
begin catch
if ERROR_NUMBER() = 8622
begin
set @SQL = replace(@SQL, '###JOINHINT###', '');
--print '--Above join hint failed, retrying SQL without join hint...' + char(13) + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @RECORDSOURCEID uniqueidentifier', @SEGMENTID = @SEGMENTID, @RECORDSOURCEID = @RECORDSOURCEID;
end
else
begin
exec dbo.[USP_RAISE_ERROR];
raiserror('Caching the current segment''s household exclusions failed.', 13, 1);
end
end catch
/****************************************/
/* End current household exclusions */
/****************************************/
end
/******************************************/
/* Previous segments household exclusions */
/******************************************/
--print '-------------------------------------------------------------------------------------------------------------------------';
--print '-- Processing households for previous segment exclusions...';
--print '-------------------------------------------------------------------------------------------------------------------------';
--Qualifying households and one record per household...
if @HOUSEHOLDINGTYPECODE = 1 and @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 1
begin
--Insert the previous segment's households into the exclusion cache table...
set @SQL = 'declare @PREVHH table([HOUSEHOLDID] ' + @HOUSEHOLDIDDATATYPE + ' primary key);' + char(13) +
char(13) +
'insert into @PREVHH ([HOUSEHOLDID])' + char(13) +
' select [HOUSEHOLDID]' + char(13) +
' from dbo.[' + @HOUSEHOLDSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ');' + char(13) +
char(13) +
'insert into dbo.[' + @HOUSEHOLDSTABLENAME + '] ([SEGMENTID], [HOUSEHOLDID])' + char(13) +
' select distinct @SEGMENTID, ' + (case when @ISBBEC = 1 then '[CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]' else '[CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' end) + char(13) +
' from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [SEG]' + char(13);
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]' + char(13);
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13);
if @SEGMENTSEQUENCE > 1
begin
if @ISBBEC = 1
set @SQL += ' left join @PREVHH as [PREVHH] on [PREVHH].[HOUSEHOLDID] = [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]' + char(13);
else
set @SQL += ' left join @PREVHH as [PREVHH] on [PREVHH].[HOUSEHOLDID] = [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' + char(13);
end
if @ISBBEC = 1
set @SQL += ' where [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] is not null';
else
set @SQL += ' where [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] is not null';
if @USEISHOUSEHOLD = 1
begin
if @ISBBEC = 1
set @SQL += char(13) + ' and [CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] = 0';
else
set @SQL += char(13) + ' and [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 0';
end
if @SEGMENTSEQUENCE > 1
set @SQL += char(13) + ' and [PREVHH].[HOUSEHOLDID] is null';
set @SQL += '###JOINHINT###;' + char(13) + char(13);
end
else
set @SQL = '';
if @SEGMENTSEQUENCE > 1 or (@SEGMENTSEQUENCE = 1 and @HOUSEHOLDINGTYPECODE = 1)
begin
set @SQL += 'with [PREVHHEXC] ([DONORID]) as' + char(13) +
'(' + char(13) +
' select ' + @CASTBEGIN + '[DONORID]' + @CASTEND + char(13) +
' from dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS;
if @HOUSEHOLDINGTYPECODE = 1
--Include the current segment when processing duplicates...
set @SQL += ', ''' + cast(@SEGMENTID as nvarchar(36)) + '''';
set @SQL += ')' + char(13) + '),' + char(13);
end
else
set @SQL += 'with ';
set @SQL += '[SEG] ([ID]) as' + char(13) +
'(' + char(13) +
' select distinct [SEG].[ID]' + char(13) +
' from ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' as [SEG]' + char(13);
if @SEGMENTSEQUENCE > 1 or (@SEGMENTSEQUENCE = 1 and @HOUSEHOLDINGTYPECODE = 1)
set @SQL += ' left join [PREVHHEXC] on [PREVHHEXC].[DONORID] = [SEG].[ID]' + char(13) +
' where [PREVHHEXC].[DONORID] is null' + char(13);
set @SQL += ')' + char(13) +
'insert into dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + '] ([SEGMENTID], [DONORID], [DUPLICATE])' + char(13);
if @HOUSEHOLDINGTYPECODE = 2 --Qualifying households
begin
--Filter down to only people...
set @SQL += ' select @SEGMENTID, [SEG].[ID], 0' + char(13) +
' from [SEG]' + char(13);
if @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = 1
begin
--Filter down to only people in households...
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization.
--We can only do this in the case where we need to include individuals with no household, because otherwise the table would exclude records we need.
set @SQL += ' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' where [CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] = 0' + char(13) +
' and [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] is not null';
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' where [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 0' + char(13) +
' and [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] is not null';
end
else
--Filter down to only people...
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' where [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 0';
end
else --Qualifying individuals
begin
if @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 1 or @USEISHOUSEHOLD = 1
begin
--Make sure we only get 'distinct' values when we add the extra 'left joins' because we can't have duplicates in this table...
set @SQL += ' select ' + (case when @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 1 and @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 0 then 'distinct ' else '' end) + '@SEGMENTID, [SEG].[ID], 0' + char(13) +
' from [SEG]' + char(13);
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]';
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]';
if @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 1
begin
--Get all households that have members...
if @ISBBEC = 1
set @SQL += char(13) +
' left join dbo.[CONSTITUENTHOUSEHOLD] as [CONSTITSWITHHOUSEHOLD] on [CONSTITSWITHHOUSEHOLD].[HOUSEHOLDID] = [CONSTITUENTHOUSEHOLD].[ID]' + char(13) +
' where [CONSTITSWITHHOUSEHOLD].[ID] is not null';
else
set @SQL += char(13) +
' left join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITSWITHHOUSEHOLD] on [CONSTITSWITHHOUSEHOLD].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] = [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + char(13) +
' where [CONSTITSWITHHOUSEHOLD].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] is not null';
end
else if @USEISHOUSEHOLD = 1
begin
--Get all households...
if @ISBBEC = 1
set @SQL += char(13) +
' where [CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] = 1';
else
set @SQL += char(13) +
' where [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 1';
end
if @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 1
set @SQL += char(13) + ' union' + char(13);
end
if @HOUSEHOLDINGONERECORDPERHOUSEHOLD = 1
begin
--Get all people with a household in a previous segment...
set @SQL += ' select @SEGMENTID, [SEG].[ID], 0' + char(13) +
' from [SEG]' + char(13);
if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' inner join dbo.[CONSTITUENTHOUSEHOLD] on ' + @CASTBEGIN + '[CONSTITUENTHOUSEHOLD].[ID]' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' inner join @PREVHH as [PREVHH] on [PREVHH].[HOUSEHOLDID] = [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID]';
else
set @SQL += ' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on ' + @CASTBEGIN + '[CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + ']' + @CASTEND + ' = [SEG].[ID]' + char(13) +
' inner join @PREVHH as [PREVHH] on [PREVHH].[HOUSEHOLDID] = [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']';
end
end
set @SQL += '###JOINHINT###;' + char(13) +
char(13) +
--Remove people from the temp segment table that are excluded because of householding...
'delete from ' + @TEMPSEGMENTATIONSEGMENTTABLE + char(13) +
'where [ID] in (' + char(13) +
' select ' + @CASTBEGIN + '[DONORID]' + @CASTEND + ' as [ID]' + char(13) +
' from dbo.[' + @HOUSEHOLDEXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ', ''' + cast(@SEGMENTID as nvarchar(36)) + ''')' + char(13) +
');';
if @NEEDCAST = 1
begin
set @SQL = replace(@SQL, @CASTBEGIN, 'cast(');
set @SQL = replace(@SQL, @CASTEND, ' as varchar(36))');
end
else
begin
set @SQL = replace(@SQL, @CASTBEGIN, '');
set @SQL = replace(@SQL, @CASTEND, '');
end
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
if @NEEDCAST = 1
set @SQLWITHJOINHINT = replace(@SQL, '###JOINHINT###', char(13) + ' option (hash join, merge join)');
else
set @SQLWITHJOINHINT = replace(@SQL, '###JOINHINT###', '');
--print @SQLWITHJOINHINT + char(13) + char(13);
exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
end try
begin catch
if ERROR_NUMBER() = 8622
begin
set @SQL = replace(@SQL, '###JOINHINT###', '');
--print '--Above join hint failed, retrying SQL without join hint...' + char(13) + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
end
else
begin
exec dbo.[USP_RAISE_ERROR];
raiserror('Caching previous segment households failed.', 13, 1);
end
end catch
/******************************************/
/* End previous household exclusions */
/******************************************/
end
/*****************************************************/
/* End householding */
/*****************************************************/
--Drop the global temp table...
if object_id('tempdb..' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE) is not null
exec ('drop table ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE);
/*******************************/
/* Previous segment exclusions */
/*******************************/
--print '-------------------------------------------------------------------------------------------------------------------------';
--print '--Previous Segment: ' + cast(@SEGMENTSEQUENCE as nvarchar(10)) + ', ' + cast(@SEGMENTID as nvarchar(36)) + ', ' + (case @RECORDSOURCETYPE when 1 then 'Record source' when 2 then 'List' when 3 then 'Consolidated list' end) + ', ' + (case when @PREVUSENTH = 1 then 'nth' else (case when @PREVUSERANDOM = 1 then 'Random' else 'N/A' end) end);
--print '-------------------------------------------------------------------------------------------------------------------------';
--Insert the previous segment's donors into the exclusion cache table...
if @MAILINGTYPECODE = 0 and @SEGMENTSEQUENCE > 1 and @RECORDSOURCETYPE = 3 and @NEEDCAST = 1
set @SQL = 'declare @EXC_DONORS_RS table([DONORID] ' + @CONSTITIDDATATYPE + ' primary key);' + char(13) +
'declare @EXC_DONORS_LISTS table([LISTDONORID] uniqueidentifier primary key);' + char(13) +
'declare @SEGMENTDONORS table([DONORID] int, [LISTDONORID] uniqueidentifier);' + char(13) +
char(13) +
'--Get the previous exclusions only for the record source...' + char(13) +
'insert into @EXC_DONORS_RS ([DONORID])' + char(13) +
' select distinct [DONORID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' and [DONORID] is not null;' + char(13) +
char(13) +
'--Get the previous exclusions only for the lists...' + char(13) +
'insert into @EXC_DONORS_LISTS ([LISTDONORID])' + char(13) +
' select distinct [LISTDONORID]' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' and [LISTDONORID] is not null;' + char(13) +
char(13);
else
set @SQL = '';
--Create and populate a temp table to use for nth or top...
if @PREVUSENTH = 1 or @PREVUSETOP = 1
begin
declare @VIEWIDDATATYPE as nvarchar(128);
declare @IDDATATYPE as nvarchar(128);
set @PREVSEGMENTTEMPTABLE = null;
if @MAILINGTYPECODE in (2, 3, 5) --membership, sponsorship, or communication revenue (although it should never get here)
set @IDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
else
set @IDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);
--Create the temp segment table for nth or top...
exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATETEMPSEGMENTTABLE] @SEGMENTID, @PREVSEGMENTTEMPTABLE output;
--Grab the datatype of the view and check if we need to cast the ID...
select @VIEWIDDATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @TEMPSEGMENTATIONSEGMENTTABLE)
and c.[name] = 'ID';
if (@ISBBEC = 0 and lower(@IDDATATYPE) = 'uniqueidentifier') or lower(@VIEWIDDATATYPE) <> lower(@IDDATATYPE)
begin
set @TEMPSQL = 'cast([SEG].[ID] as ' + @IDDATATYPE + ')';
set @TEMPNEEDCAST = 1;
end
else
begin
set @TEMPSQL = '[SEG].[ID]';
set @TEMPNEEDCAST = 0;
end
--Insert all the segment donor IDs into the temp table...
set @TEMPSQL = 'insert into dbo.[' + @PREVSEGMENTTEMPTABLE + '] ([ID])' + char(13) +
' select ' + @TEMPSQL + char(13) +
' from ' + @TEMPSEGMENTATIONSEGMENTTABLE + ' as [SEG]' + char(13) +
(case when @PREVUSETOP = 1 then ' left join [' + @CONSTITRANKVIEWNAME + '] as [CONSTITSRANK] on [CONSTITSRANK].[ID] = [SEG].[ID]' + char(13) else '' end) +
' where [SEG].[ID] is not null' +
(case when @PREVUSETOP = 1 then char(13) + ' order by [CONSTITSRANK].[' + @CONSTITRANKVALUECOLUMN + ']' + (case when @CONSTITRANKORDERDESC = 1 then ' desc' else '' end) else '' end);
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
if @TEMPNEEDCAST = 1
set @SQLWITHJOINHINT = @TEMPSQL + char(13) + ' option (hash join, merge join)';
else
set @SQLWITHJOINHINT = @TEMPSQL;
exec (@SQLWITHJOINHINT);
end try
begin catch
if ERROR_NUMBER() = 8622
exec (@TEMPSQL);
else
begin
exec dbo.[USP_RAISE_ERROR];
raiserror('Retrieving the segment record count by nth sample size failed.', 13, 1);
end
end catch
end
else
set @PREVSEGMENTTEMPTABLE = @TEMPSEGMENTATIONSEGMENTTABLE;
--Get the record count from the temp table...
set @TEMPSQL = 'select @PREVRECORDS = count([ID]) from dbo.[' + @PREVSEGMENTTEMPTABLE + ']';
exec sp_executesql @TEMPSQL, N'@PREVRECORDS int output', @PREVRECORDS = @PREVRECORDS output;
--If this segment's remainders will fall through to other segments, then calculate the offer count if samplesize is not 100%...
if @PREVUSENTH = 1 or @PREVUSERANDOM = 1 or @PREVUSETOP = 1
begin
if @SAMPLESIZETYPECODE = 0 --Percent
begin
set @PREVOFFERS = floor(cast(@PREVRECORDS as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100));
end
else --Records
begin
if @PREVRECORDS > @SAMPLESIZE
set @PREVOFFERS = @SAMPLESIZE;
else
set @PREVOFFERS = @PREVRECORDS;
end
end
if @MAILINGTYPECODE = 0 and @SEGMENTSEQUENCE > 1 and @RECORDSOURCETYPE = 3 and @NEEDCAST = 1
set @SQL = @SQL + '--Split out the segment donor IDs into their respective data types (to speed up this SQL)...' + char(13) +
'insert into @SEGMENTDONORS ([DONORID], [LISTDONORID])' + char(13) +
' select' + (case when (@PREVUSERANDOM = 1 or @PREVUSETOP = 1) then ' top(@PREVOFFERS)' else '' end) + char(13) +
' (case when patindex(''[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]'', [SEG].[ID]) = 0 then [SEG].[ID] else null end),' + char(13) +
' (case when patindex(''[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]-[A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9][A-Fa-f0-9]'', [SEG].[ID]) > 0 then [SEG].[ID] else null end)' + char(13) +
' from dbo.[' + @PREVSEGMENTTEMPTABLE + '] as [SEG]';
else
begin
if @MAILINGTYPECODE = 0 and @RECORDSOURCETYPE = 1 and @SEGMENTSEQUENCE > 1 and @NEEDCAST = 0
begin
--Grab the datatype of the view and check if we need to cast the ID...
select @VIEWIDDATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @PREVSEGMENTTEMPTABLE)
and c.[name] = 'ID';
if lower(@VIEWIDDATATYPE) <> lower(@CONSTITIDDATATYPE)
set @NEEDCAST = 1;
end
set @SQL = @SQL + '--Insert this segment''s donor IDs into the exclusion table...' + char(13);
if @MAILINGTYPECODE = 0
begin
if @RECORDSOURCETYPE = 1 and @SEGMENTSEQUENCE > 1 and @NEEDCAST = 1
--This intermediate temp table is needed in order to work around some strange casting issues...
set @SQL = @SQL + 'declare @TEMP table([ID] varchar(36));' + char(13) +
'insert into @TEMP ([ID])' + char(13);
else
set @SQL = @SQL + 'insert into dbo.[' + @EXCLUSIONSTABLENAME + '] ([SEGMENTID], [DONORID], [LISTDONORID])' + char(13);
end
else
set @SQL = @SQL + 'insert into dbo.[' + @EXCLUSIONSTABLENAME + '] ([SEGMENTID], [' + @EXCLUSIONSTABLEIDFIELDNAME + '])' + char(13);
set @SQL = @SQL + ' select ' + (case when (@PREVUSERANDOM = 1 or @PREVUSETOP = 1) then 'top(@PREVOFFERS) ' else '' end);
if @MAILINGTYPECODE = 0
begin
if @RECORDSOURCETYPE = 1 --Record source
begin
if @SEGMENTSEQUENCE > 1 and @NEEDCAST = 1
set @SQL = @SQL + '[SEG].[ID]' + char(13);
else
set @SQL = @SQL + '@SEGMENTID, [SEG].[ID], null' + char(13);
end
else if @RECORDSOURCETYPE = 2 --List
set @SQL = @SQL + '@SEGMENTID, null, [SEG].[ID]' + char(13);
else if @RECORDSOURCETYPE = 3 --Consolidated list
set @SQL = @SQL + '@SEGMENTID, [CONSOL_RECORDSOURCE].[ID], [CONSOL_LISTS].[ID]' + char(13);
end
else
set @SQL = @SQL + '@SEGMENTID, [SEG].[ID]' + char(13);
set @SQL = @SQL + ' from dbo.[' + @PREVSEGMENTTEMPTABLE + '] as [SEG]';
--Since the mailing segment views already exclude all previous segment donors, then we
--only need to explicitly exclude previous segment donors when we are not selecting from
--the segment view, and that only happens when @PREVUSENTH = 1 or @PREVUSETOP = 1.
if @SEGMENTSEQUENCE > 1 and (@PREVUSENTH = 1 or @PREVUSETOP = 1)
begin
set @SQL = @SQL + char(13) +
' left join (' + char(13);
if @MAILINGTYPECODE = 0
begin
if @RECORDSOURCETYPE = 3
set @SQL += ' select ' + @CASTBEGIN + 'isnull([DONORID], [LISTDONORID])' + @CASTEND + ' as [ID]' + char(13);
else if @RECORDSOURCETYPE = 2
set @SQL += ' select ' + @CASTBEGIN + '[LISTDONORID]' + @CASTEND + ' as [ID]' + char(13);
else
set @SQL += ' select ' + @CASTBEGIN + '[DONORID]' + @CASTEND + ' as [ID]' + char(13);
end
else
set @SQL += ' select ' + @CASTBEGIN + '[' + @EXCLUSIONSTABLEIDFIELDNAME + ']' + @CASTEND + ' as [ID]' + char(13);
set @SQL += ' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] in (' + @PREVSEGMENTIDS + ')' + char(13) +
' ) as [PREVSEGEXC] on [PREVSEGEXC].[ID] = [SEG].[ID]';
end
if @MAILINGTYPECODE = 0 and @RECORDSOURCETYPE = 3 --Consolidated list
set @SQL = @SQL + char(13) +
' left join dbo.[' + @CONSOLIDATEDVIEW + '] as [CONSOL_RECORDSOURCE] on [CONSOL_RECORDSOURCE].[SOURCEQUERYVIEWID] = @RECORDSOURCEID and [CONSOL_RECORDSOURCE].[ID] = ' + @CASTBEGIN + '[SEG].[ID]' + @CASTEND + char(13) +
' left join dbo.[' + @CONSOLIDATEDVIEW + '] as [CONSOL_LISTS] on [CONSOL_LISTS].[SOURCEQUERYVIEWID] <> @RECORDSOURCEID and [CONSOL_LISTS].[ID] = ' + @CASTBEGIN + '[SEG].[ID]' + @CASTEND + char(13) +
' where ([CONSOL_RECORDSOURCE].[ID] is not null or [CONSOL_LISTS].[ID] is not null)';
--Get all the previous segment exclusions and make sure we do not insert duplicates into the exclusions table...
if @SEGMENTSEQUENCE > 1 and (@PREVUSENTH = 1 or @PREVUSETOP = 1)
begin
if @RECORDSOURCETYPE = 3 --Consolidated list
set @SQL = @SQL + char(13) + ' and';
else
set @SQL = @SQL + char(13) + ' where';
set @SQL = @SQL + ' [PREVSEGEXC].[ID] is null';
end
if @NEEDCAST = 1
begin
set @SQL = replace(@SQL, @CASTBEGIN, 'cast(');
set @SQL = replace(@SQL, @CASTEND, ' as varchar(36))');
end
else
begin
set @SQL = replace(@SQL, @CASTBEGIN, '');
set @SQL = replace(@SQL, @CASTEND, '');
end
end
if @PREVUSETOP = 1
begin
--print '@PREVRECORDS = ' + isnull(cast(@PREVRECORDS as nvarchar(10)), 'null');
--print '@PREVOFFERS = ' + isnull(cast(@PREVOFFERS as nvarchar(10)), 'null');
set @SQL = @SQL + char(13) + ' order by [SEG].[ROW]';
end
else if @PREVUSENTH = 1
begin
--If @PREVOFFERS is more than half of @PREVRECORDS, then Nth record will come out to every 1 record, which isn't very good.
--So if Nth record is 1, then reverse the Nth calculation so we get a better distribution of records than every 1 record.
--print '@PREVRECORDS = ' + cast(@PREVRECORDS as nvarchar(10));
--print '@PREVOFFERS = ' + cast(@PREVOFFERS as nvarchar(10));
if (@PREVRECORDS > @PREVOFFERS) and (@PREVOFFERS > 0)
begin
set @SQL = @SQL + char(13) + ' ' + (case when @SEGMENTSEQUENCE > 1 then (case when @RECORDSOURCETYPE = 3 and @NEEDCAST = 1 then 'where' else 'and' end) else (case when @RECORDSOURCETYPE = 3 then 'and' else 'where' end) end) + ' floor(([SEG].[ROW] - 1) % @PREVNTHRECORD) ';
set @PREVNTHRECORD = cast(@PREVRECORDS as decimal(20,5)) / cast(@PREVOFFERS as decimal(20,5));
if floor(@PREVNTHRECORD) = 1
begin
set @PREVNTHRECORD = cast(@PREVRECORDS as decimal(20,5)) / cast((@PREVRECORDS - @PREVOFFERS) as decimal(20,5));
set @SQL = @SQL + '<> 0';
--print '@PREVNTHRECORD <> ' + cast(@PREVNTHRECORD as nvarchar(30));
end
else
begin
set @SQL = @SQL + '= 0';
--print '@PREVNTHRECORD = ' + cast(@PREVNTHRECORD as nvarchar(30));
end
end
end
else if @PREVUSERANDOM = 1
begin
--print '@PREVRECORDS = ' + isnull(cast(@PREVRECORDS as nvarchar(10)), 'null');
--print '@PREVOFFERS = ' + isnull(cast(@PREVOFFERS as nvarchar(10)), 'null');
set @SQL = @SQL + char(13) + ' order by NewID()';
end
if @MAILINGTYPECODE = 0 and @SEGMENTSEQUENCE > 1 and @NEEDCAST = 1
begin
if @RECORDSOURCETYPE = 1
set @SQL = @SQL + '###JOINHINT###' + char(13) +
'insert into dbo.[' + @EXCLUSIONSTABLENAME + '] ([SEGMENTID], [DONORID], [LISTDONORID])' + char(13) +
' select @SEGMENTID, [ID], null' + char(13) +
' from @TEMP;';
else if @RECORDSOURCETYPE = 3
set @SQL = @SQL + '###JOINHINT###' + char(13) +
'--Insert this segment''s donor IDs into the exclusion table...' + char(13) +
'insert into dbo.[' + @EXCLUSIONSTABLENAME + '] ([SEGMENTID], [DONORID], [LISTDONORID])' + char(13) +
' --Record source' + char(13) +
' select @SEGMENTID, [DONORS].[DONORID], null' + char(13) +
' from @SEGMENTDONORS as [DONORS]' + char(13) +
' where [DONORS].[DONORID] is not null' + char(13) +
' and [DONORS].[DONORID] not in (select [DONORID] from @EXC_DONORS_RS)' + char(13) +
' union all' + char(13) +
' --Lists' + char(13) +
' select @SEGMENTID, null, [DONORS].[LISTDONORID]' + char(13) +
' from @SEGMENTDONORS as [DONORS]' + char(13) +
' where [DONORS].[LISTDONORID] is not null' + char(13) +
' and [DONORS].[LISTDONORID] not in (select [LISTDONORID] from @EXC_DONORS_LISTS);';
end
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
if @SEGMENTSEQUENCE > 1 and @RECORDSOURCETYPE in (1, 3) and @NEEDCAST = 1
set @SQLWITHJOINHINT = replace(@SQL, '###JOINHINT###', char(13) + ' option (hash join, merge join);' + char(13));
else if @NEEDCAST = 1
set @SQLWITHJOINHINT = @SQL + char(13) + ' option (hash join, merge join)';
else
set @SQLWITHJOINHINT = @SQL;
--print @SQLWITHJOINHINT + char(13) + char(13);
exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @RECORDSOURCEID uniqueidentifier, @PREVOFFERS int, @PREVNTHRECORD decimal(20,5)', @SEGMENTID = @SEGMENTID, @RECORDSOURCEID = @RECORDSOURCEID, @PREVOFFERS = @PREVOFFERS, @PREVNTHRECORD = @PREVNTHRECORD;
end try
begin catch
if ERROR_NUMBER() = 8622
begin
if @SEGMENTSEQUENCE > 1 and @RECORDSOURCETYPE in (1, 3) and @NEEDCAST = 1
set @SQL = replace(@SQL, '###JOINHINT###', ';' + char(13));
--print '--Above join hint failed, retrying SQL without join hint...' + char(13) + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @RECORDSOURCEID uniqueidentifier, @PREVOFFERS int, @PREVNTHRECORD decimal(20,5)', @SEGMENTID = @SEGMENTID, @RECORDSOURCEID = @RECORDSOURCEID, @PREVOFFERS = @PREVOFFERS, @PREVNTHRECORD = @PREVNTHRECORD;
end
else
begin
exec dbo.[USP_RAISE_ERROR];
raiserror('Caching previous segment exclusions failed.', 13, 1);
end
end catch
if (@PREVUSENTH = 1 or @PREVUSETOP = 1) and object_id('tempdb..' + @PREVSEGMENTTEMPTABLE) is not null
exec ('drop table dbo.[' + @PREVSEGMENTTEMPTABLE + ']');
--When doing householding, the householding code runs before the exclusion code so the householding code
--doesn't know which records to include when the segment is not 100%. So, after we have calculated the
--exclusions, go back and delete the households that got excluded.
if @USEHOUSEHOLDING = 1 and @HOUSEHOLDINGTYPECODE = 1
begin
set @SQL = 'declare @HOUSEHOLDS table ([HOUSEHOLDID] ' + @HOUSEHOLDIDDATATYPE + ' primary key);' + char(13) +
char(13) +
'--Grab all the households for excluded donors in the segment...' + char(13) +
'insert into @HOUSEHOLDS ([HOUSEHOLDID])' + char(13);
if @USEISHOUSEHOLD = 0
set @SQL = @SQL +
' select distinct [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + ']' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXC]' + char(13) +
' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] = [EXC].[DONORID]' + char(13) +
' where [EXC].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] is not null;' + char(13);
else if @ISBBEC = 1
--For BBEC, we can bypass the constituent view altogether and just use the CONSTITUENTHOUSEHOLD table as an optimization...
set @SQL += ' select distinct (case when [CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] = 1 then [CONSTITUENTHOUSEHOLD].[ID] else [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] end)' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXC]' + char(13) +
' inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = [EXC].[DONORID]' + char(13) +
' where [EXC].[SEGMENTID] = @SEGMENTID' + char(13) +
' and ([CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] is not null or [CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD] = 1);' + char(13);
else
set @SQL += ' select distinct (case when [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 1 then [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] else [CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] end)' + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXC]' + char(13) +
' inner join dbo.[' + @CONSTITVIEWNAME + '] as [CONSTITS] on [CONSTITS].[' + @CONSTITVIEWPRIMARYKEYFIELD + '] = [EXC].[DONORID]' + char(13) +
' where [EXC].[SEGMENTID] = @SEGMENTID' + char(13) +
' and ([CONSTITS].[' + @CONSTITVIEWHOUSEHOLDIDFIELD + '] is not null or [CONSTITS].[' + @CONSTITVIEWISHOUSEHOLDFIELD + '] = 1);' + char(13);
set @SQL = @SQL + char(13) +
'--Delete any households that got excluded because of the donor exclusions we calculated...' + char(13) +
'delete from dbo.[' + @HOUSEHOLDSTABLENAME + ']' + char(13) +
'from dbo.[' + @HOUSEHOLDSTABLENAME + '] as [HH]' + char(13) +
'left join @HOUSEHOLDS as [HOUSEHOLDS] on [HOUSEHOLDS].[HOUSEHOLDID] = [HH].[HOUSEHOLDID]' + char(13) +
'where [HH].[SEGMENTID] = @SEGMENTID' + char(13) +
'and [HOUSEHOLDS].[HOUSEHOLDID] is null;';
--print '-------------------------------------------------------------------------------------------------------------------------';
--print @SQL + char(13) + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
end
--Update the cache date for the segment exclusion info...
if exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] where [SEGMENTID] = @SEGMENTID)
update dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] set
[RECORDCOUNT] = @PREVRECORDS
where [SEGMENTID] = @SEGMENTID;
else
insert into dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] (
[SEGMENTID],
[RECORDCOUNT]
) values (
@SEGMENTID,
@PREVRECORDS
);
/*******************************/
/* End previous exclusions */
/*******************************/
--Release the applock now that we are done with this segment...
exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @MKTSEGMENTID, 0;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
set @RETVAL = 1;
--Drop the global temp table (if it exists)...
if object_id('tempdb..' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE) is not null
exec ('drop table ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE);
--Release the last segment lock before the failure occurred...
if @MKTSEGMENTID is not null
exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @MKTSEGMENTID, 0;
end catch
--Release the app lock for this segment...
exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';
return @RETVAL;