USP_MKTSEGMENTATIONACTIVATE_ACTIVATESEGMENT
Activates a segment by inserting the donor IDs into the marketing effort data table.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTID | uniqueidentifier | IN | |
| @TESTSEGMENTID | uniqueidentifier | IN | |
| @MAXROWS | int | IN | |
| @USEMAXROWS | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_ACTIVATESEGMENT]
(
@SEGMENTID uniqueidentifier,
@TESTSEGMENTID uniqueidentifier = null,
@MAXROWS int = 0,
@USEMAXROWS bit = 1
)
as
set nocount on;
declare @SEGMENTATIONID uniqueidentifier;
declare @MAILINGTYPECODE tinyint;
declare @SEGMENTATIONISHISTORICAL bit;
declare @ISACTIVATING bit;
declare @DATATABLE nvarchar(128);
declare @INDEXNAME nvarchar(128);
declare @INSERTSQL nvarchar(max);
declare @SQL nvarchar(max);
declare @USETOP bit;
declare @USENTH bit;
declare @USERANDOM bit;
declare @SEGMENTRECORDSOURCEID uniqueidentifier;
declare @SEGMENTRECORDTYPEID uniqueidentifier;
declare @SEGMENTRECORDTYPE tinyint;
declare @CONSOLIDATEDVIEWNAME nvarchar(128);
declare @TEMPSEGMENTTABLENAME nvarchar(128);
declare @TEMPNTHTABLENAME nvarchar(128);
declare @RECORDCOUNT int;
declare @NTHRECORD decimal(30,20);
declare @IDFORBUILDSOURCECODE uniqueidentifier;
declare @SOURCEQUERYVIEWID uniqueidentifier;
declare @SOURCECODE nvarchar(50);
declare @SAMPLESIZEEXCLUDEREMAINDER bit;
declare @NEEDCAST bit;
declare @LISTNAME nvarchar(50);
declare @LISTTABLENAME nvarchar(128);
declare @GIFTVIEWNAME nvarchar(128);
declare @GIFTVIEWPRIMARYKEYFIELD nvarchar(255);
declare @GIFTVIEWDONORIDFIELD nvarchar(255);
declare @MKTSEGMENTID uniqueidentifier;
declare @MEMBERVIEWNAME nvarchar(128);
declare @MEMBERVIEWPRIMARYKEYFIELD nvarchar(255);
declare @MEMBERVIEWDONORIDFIELD nvarchar(255);
declare @SPONSORVIEWNAME nvarchar(128);
declare @SPONSORVIEWPRIMARYKEYFIELD nvarchar(255);
declare @SPONSORVIEWDONORIDFIELD nvarchar(255);
declare @DATATYPE nvarchar(128);
declare @CONSTITUENTFILEIMPORTUSED bit;
declare @ISBBEC bit;
begin try
if @MAXROWS is null or @MAXROWS < 0
set @MAXROWS = 0;
if @TESTSEGMENTID is null
--Grab info from the segment
select distinct
@SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@SEGMENTATIONISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
@ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]),
@USENTH = (case when [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE] = 0 and not ([MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE] = 0 and [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] = 100) then 1 else 0 end),
@USERANDOM = (case when [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE] = 1 and not ([MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE] = 0 and [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] = 100) then 1 else 0 end),
@USETOP = (case when [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE] = 2 and not ([MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE] = 0 and [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] = 100) then 1 else 0 end),
@SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
@SEGMENTRECORDTYPEID = [IDSETREGISTER].[RECORDTYPEID],
@SEGMENTRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
@IDFORBUILDSOURCECODE = @SEGMENTID,
@SAMPLESIZEEXCLUDEREMAINDER = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
@MKTSEGMENTID = [MKTSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID] or [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
else
--Grab info from the test segment
select distinct
@SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@SEGMENTATIONISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
@ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]),
@USENTH = (case when [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZEMETHODCODE] = 0 then 1 else 0 end),
@USERANDOM = (case when [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZEMETHODCODE] = 1 then 1 else 0 end),
@USETOP = (case when [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZEMETHODCODE] = 2 then 1 else 0 end),
@SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
@SEGMENTRECORDTYPEID = [IDSETREGISTER].[RECORDTYPEID],
@SEGMENTRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
@IDFORBUILDSOURCECODE = @TESTSEGMENTID,
@SAMPLESIZEEXCLUDEREMAINDER = 0,
@MKTSEGMENTID = [MKTSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID] or [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
if @SEGMENTATIONISHISTORICAL = 0
begin
set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
set @TEMPSEGMENTTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETEMPSEGMENTTABLENAME](@SEGMENTID);
set @CONSTITUENTFILEIMPORTUSED = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID);
set @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@SEGMENTRECORDSOURCEID);
if @MAILINGTYPECODE in (1, 5)
begin
if @ISBBEC = 1
begin
set @GIFTVIEWNAME = 'FINANCIALTRANSACTION';
set @GIFTVIEWPRIMARYKEYFIELD = 'ID';
set @GIFTVIEWDONORIDFIELD = 'CONSTITUENTID';
end
else
select
@GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@GIFTVIEWDONORIDFIELD = [MKTGIFTRECORDSOURCE].[DONORIDFIELD]
from dbo.[MKTGIFTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTGIFTRECORDSOURCE].[ID] = @SEGMENTRECORDSOURCEID;
end
else if @MAILINGTYPECODE = 2
begin
if @ISBBEC = 1
begin
set @MEMBERVIEWNAME = 'MEMBER';
set @MEMBERVIEWPRIMARYKEYFIELD = 'MEMBERSHIPID';
set @MEMBERVIEWDONORIDFIELD = 'CONSTITUENTID';
end
else
select
@MEMBERVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@MEMBERVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@MEMBERVIEWDONORIDFIELD = [MKTMEMBERSHIPRECORDSOURCE].[MEMBERIDFIELD]
from dbo.[MKTMEMBERSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTMEMBERSHIPRECORDSOURCE].[ID] = @SEGMENTRECORDSOURCEID;
end
else if @MAILINGTYPECODE = 3
begin
if @ISBBEC = 1
begin
set @SPONSORVIEWNAME = 'SPONSORSHIP';
set @SPONSORVIEWPRIMARYKEYFIELD = 'ID';
set @SPONSORVIEWDONORIDFIELD = 'CONSTITUENTID';
end
else
select
@SPONSORVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@SPONSORVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@SPONSORVIEWDONORIDFIELD = [MKTSPONSORSHIPRECORDSOURCE].[SPONSORIDFIELD]
from dbo.[MKTSPONSORSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTSPONSORSHIPRECORDSOURCE].[ID] = @SEGMENTRECORDSOURCEID;
end
set @INSERTSQL = '--Insert all the donors for this segment into the activated marketing effort data table...' + char(13) +
'insert into dbo.[' + @DATATABLE + '] (' + char(13) +
' [SEGMENTID], ' + char(13) +
' [TESTSEGMENTID], ' + char(13) +
(case when @MAILINGTYPECODE in (1, 5) then ' [REVENUEID], ' + char(13) else '' end) +
(case when @MAILINGTYPECODE = 2 then ' [MEMBERSHIPID], ' + char(13) else '' end) +
(case when @MAILINGTYPECODE = 3 then ' [SPONSORSHIPID], ' + char(13) else '' end) +
' [DONORID], ' + char(13) +
' [DONORQUERYVIEWCATALOGID], ' + char(13) +
' [SOURCECODE])' + char(13);
if @TESTSEGMENTID is not null
begin
--Create another temp table to hold all the IDs that we are going to insert into the mailing data table so
--that we can remove only these IDs from the segment temp table below before we process the next test segment.
set @TEMPNTHTABLENAME = '##TEMP_NTH_' + replace(cast(@SEGMENTID as nvarchar(36)), '-', '_');
if @MAILINGTYPECODE = 0
set @DATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);
else
set @DATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
declare @CREATESQL nvarchar(max);
set @CREATESQL = 'create table dbo.[' + @TEMPNTHTABLENAME + '] ([ID] ' + @DATATYPE + ' not null primary key' + (case when @MAILINGTYPECODE in (1, 5) and @ISBBEC = 1 then ', [DONORID] ' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID) + ' not null' else '' end) + ')';
exec (@CREATESQL);
set @INSERTSQL = @INSERTSQL + 'output INSERTED.[' + (case @MAILINGTYPECODE when 1 then 'REVENUEID' when 2 then 'MEMBERSHIPID' when 3 then 'SPONSORSHIPID' when 5 then 'REVENUEID' else 'DONORID' end) + ']' + (case when @MAILINGTYPECODE in (1, 5) and @ISBBEC = 1 then ', INSERTED.[DONORID]' else '' end) + ' into dbo.[' + @TEMPNTHTABLENAME + ']' + char(13);
end
if @MAILINGTYPECODE = 0 and @SEGMENTRECORDTYPE = 3
begin
--Get the consolidated view name if the source query view id for this segment is a consolidated view.
select
@CONSOLIDATEDVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@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] = @SEGMENTRECORDSOURCEID;
set @SQL = 'declare @CONSOLIDATEDSOURCECODETABLE table([SOURCEQUERYVIEWID] uniqueidentifier primary key, [SOURCECODE] nvarchar(255));' + char(13);
if @NEEDCAST = 1
begin
--Get the donor ID data type from the record source query view...
select
@DATATYPE = [PRIMARYKEYTYPENAME]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @SEGMENTRECORDSOURCEID;
set @SQL = @SQL + 'declare @SEGMENTDONORS table([DONORID] ' + @DATATYPE + ', [LISTDONORID] uniqueidentifier);' + char(13);
end
set @SQL = @SQL + char(13) +
'--Get all the source query view IDs (and their sourcecodes) that make up the consolidated list...' + char(13) +
'insert into @CONSOLIDATEDSOURCECODETABLE ([SOURCEQUERYVIEWID], [SOURCECODE])' + char(13) +
' select @SEGMENTRECORDSOURCEID, dbo.[UFN_MKTSOURCECODE_BUILDCODE](@IDFORBUILDSOURCECODE, @SEGMENTRECORDSOURCEID, default)' + char(13) +
' union all' + char(13) +
' select [MKTSEGMENTLIST].[QUERYVIEWCATALOGID], dbo.[UFN_MKTSOURCECODE_BUILDCODE](@IDFORBUILDSOURCECODE, [MKTSEGMENTLIST].[QUERYVIEWCATALOGID], default)' + char(13) +
' from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]' + char(13) +
' inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]' + char(13) +
' where [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = @SEGMENTRECORDSOURCEID;' + char(13) +
char(13);
if @NEEDCAST = 1
-- if a test segment is slated to receive 0 offers, then it needs to be forced to have 0 offers,
-- hence the or @TESTSEGMENTID is not null
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 @USEMAXROWS = 1 then ' top(@MAXROWS)' 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]'', [ID]) = 0 then [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]'', [ID]) > 0 then [ID] else null end)' + char(13);
end
else
begin
--Get the record source query view ID if the segment came from a record source record type...
select @SOURCEQUERYVIEWID = [MKTRECORDSOURCE].[ID]
from dbo.[MKTRECORDSOURCE]
inner join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
left outer join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
left outer join dbo.[MKTSPONSORSHIPRECORDSOURCE] on [MKTSPONSORSHIPRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
inner join dbo.[QUERYVIEWCATALOG] on ([QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID] or [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] or [QUERYVIEWCATALOG].[ID] = isnull([MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID], convert(uniqueidentifier, '00000000-0000-0000-0000-000000000000')) or [QUERYVIEWCATALOG].[ID] = isnull([MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID], convert(uniqueidentifier, '00000000-0000-0000-0000-000000000000')))
left outer join dbo.[RECORDTYPECOMPATIBILITYMAP] as [MAP] on [MAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @SEGMENTRECORDTYPEID or [MAP].[RECORDTYPE2ID] = @SEGMENTRECORDTYPEID)
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
if @SOURCEQUERYVIEWID is null
--Segment is based on a list, so get the query view ID of the list...
select @SOURCEQUERYVIEWID = [QUERYVIEWCATALOGID]
from dbo.[MKTSEGMENTLIST]
where [IDSETRECORDTYPEID] = @SEGMENTRECORDTYPEID;
set @SOURCECODE = dbo.[UFN_MKTSOURCECODE_BUILDCODE](@IDFORBUILDSOURCECODE, default, default);
end
if @SEGMENTRECORDTYPE <> 3 or (@SEGMENTRECORDTYPE = 3 and @NEEDCAST = 0)
begin
-- if a test segment is slated to receive 0 offers, then it needs to be forced to have 0 offers,
-- hence the or @TESTSEGMENTID is not null
set @SQL = isnull(@SQL, '') +
@INSERTSQL +
' select' + (case when @USEMAXROWS = 1 then ' top(@MAXROWS)' else '' end) + char(13) +
' @SEGMENTID,' + char(13) +
' ' + (case when @TESTSEGMENTID is null then 'null' else '@TESTSEGMENTID' end) + ',' + char(13) +
' [TEMP].[ID],' + char(13);
if @MAILINGTYPECODE = 1
begin
if @ISBBEC = 1
--For BBEC, we need to use the acknowledgee ID instead of the gift donor ID...
set @SQL = @SQL + ' [TEMP].[DONORID],' + char(13);
else
set @SQL = @SQL + ' [GIFTS].[' + @GIFTVIEWDONORIDFIELD + '],' + char(13);
end
else if @MAILINGTYPECODE = 2
set @SQL = @SQL + ' [MEMBERS].[' + @MEMBERVIEWDONORIDFIELD + '],' + char(13);
else if @MAILINGTYPECODE = 3
set @SQL = @SQL + ' [SPONSORS].[' + @SPONSORVIEWDONORIDFIELD + '],' + char(13);
else if @MAILINGTYPECODE = 5
set @SQL = @SQL + ' [GIFTS].[' + @GIFTVIEWDONORIDFIELD + '],' + char(13);
if @SEGMENTRECORDTYPE <> 3
set @SQL = @SQL +
' @SOURCEQUERYVIEWID,' + char(13) +
' @SOURCECODE' + char(13);
else
set @SQL = @SQL +
' [TEMPSOURCECODETABLE].[SOURCEQUERYVIEWID],' + char(13) +
' [TEMPSOURCECODETABLE].[SOURCECODE]' + char(13);
end
set @SQL = @SQL + ' from dbo.[' + @TEMPSEGMENTTABLENAME + '] as [TEMP]';
if @MAILINGTYPECODE = 1
begin
--We don't have to worry about BBEC here because the appropriate joins happen in the populate USP...
if @ISBBEC <> 1
set @SQL = @SQL + char(13) + ' inner join dbo.[' + @GIFTVIEWNAME + '] as [GIFTS] on [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + '] = [TEMP].[ID]';
end
else if @MAILINGTYPECODE = 2
set @SQL = @SQL + char(13) +
' inner join dbo.[' + @MEMBERVIEWNAME + '] as [MEMBERS] on [MEMBERS].[' + @MEMBERVIEWPRIMARYKEYFIELD + '] = [TEMP].[ID] and [MEMBERS].[ISPRIMARY] = 1 and [MEMBERS].[ISDROPPED] = 0' + char(13) +
' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBERS].[' + @MEMBERVIEWPRIMARYKEYFIELD + '] and [MEMBERSHIP].[STATUSCODE] <> 1';
else if @MAILINGTYPECODE = 3
set @SQL = @SQL + char(13) + ' inner join dbo.[' + @SPONSORVIEWNAME + '] as [SPONSORS] on [SPONSORS].[' + @SPONSORVIEWPRIMARYKEYFIELD + '] = [TEMP].[ID]';
else if @SEGMENTRECORDTYPE = 3 and @NEEDCAST = 0
set @SQL = @SQL + char(13) + ' inner join dbo.[' + @CONSOLIDATEDVIEWNAME + '] as [CONSOL] on [CONSOL].[ID] = [TEMP].[ID]' + char(13) +
' inner join @CONSOLIDATEDSOURCECODETABLE as [TEMPSOURCECODETABLE] on [TEMPSOURCECODETABLE].[SOURCEQUERYVIEWID] = [CONSOL].[SOURCEQUERYVIEWID]';
else if @MAILINGTYPECODE = 5
begin
set @SQL = @SQL + char(13) + ' inner join dbo.[' + @GIFTVIEWNAME + '] as [GIFTS] on [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + '] = [TEMP].[ID]';
if @ISBBEC = 1
set @SQL = @SQL + ' and [GIFTS].[DELETEDON] is null' + char(13) +
' inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + ']';
end
--Only perform nth or random or top if this is a test segment or if this is a segment that excludes its remainder from the rest of the segments.
--All other segments have already been through this nth or random or top algorithm, so don't do it again.
if @TESTSEGMENTID is not null or @SAMPLESIZEEXCLUDEREMAINDER = 1
begin
if @CONSTITUENTFILEIMPORTUSED = 1 and @TESTSEGMENTID is not null
begin
declare @CONSTITUENTFILEIMPORTTABLE nvarchar(256);
set @CONSTITUENTFILEIMPORTTABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
set @SQL = @SQL + char(13) + ' inner join (select distinct [RECORDID], [SEGMENTATIONTESTSEGMENTID] from dbo.[' + @CONSTITUENTFILEIMPORTTABLE + ']) as [IMPORT] on [IMPORT].[RECORDID] = [TEMP].[ID] and [IMPORT].[SEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID';
end
else
begin
if @USETOP = 1
begin
--Force the top-ranked set of IDs by using this order by clause
set @SQL = @SQL + char(13) + ' order by [TEMP].[ROW]';
end
else if @USENTH = 1
begin
--Grab only every Nth record...
declare @COUNTSQL nvarchar(max);
set @COUNTSQL = 'select @RECORDCOUNT = count([ID]) from dbo.[' + @TEMPSEGMENTTABLENAME + ']';
exec sp_executesql @COUNTSQL, N'@RECORDCOUNT int output', @RECORDCOUNT = @RECORDCOUNT output;
--If @MAXROWS is more than half of @RECORDCOUNT, 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.
if (@RECORDCOUNT > @MAXROWS) and (@MAXROWS > 0)
begin
set @SQL = @SQL + char(13) + ' where ';
set @SQL = @SQL + 'floor(([TEMP].[ROW] - 1) % @NTHRECORD) ';
set @NTHRECORD = cast(@RECORDCOUNT as decimal(20,8)) / cast(@MAXROWS as decimal(20,8));
if floor(@NTHRECORD) = 1
begin
set @NTHRECORD = cast(@RECORDCOUNT as decimal(20,8)) / cast((@RECORDCOUNT - @MAXROWS) as decimal(20,8));
set @SQL = @SQL + '<> 0';
end
else
set @SQL = @SQL + '= 0';
set @SQL = @SQL + char(13) + ' order by [TEMP].[ROW]';
end
end
else if @USERANDOM = 1
begin
--Force a random set of IDs by using this order by clause
set @SQL = @SQL + char(13) + ' order by NewID()';
end
end
end
if @MAILINGTYPECODE = 0 and @SEGMENTRECORDTYPE = 3 and @NEEDCAST = 1
set @SQL = @SQL + ';' + char(13) +
char(13) +
@INSERTSQL +
' --Record source' + char(13) +
' select' + char(13) +
' @SEGMENTID,' + char(13) +
' ' + (case when @TESTSEGMENTID is null then 'null' else '@TESTSEGMENTID' end) + ',' + char(13) +
' cast([DONORID] as varchar(36)),' + char(13) +
' @SEGMENTRECORDSOURCEID,' + char(13) +
' (select [SOURCECODE] from @CONSOLIDATEDSOURCECODETABLE where [SOURCEQUERYVIEWID] = @SEGMENTRECORDSOURCEID)' + char(13) +
' from @SEGMENTDONORS as [DONORS]' + char(13) +
' where [DONORID] is not null' + char(13) +
' union all' + char(13) +
' --Lists' + char(13) +
' select' + char(13) +
' @SEGMENTID,' + char(13) +
' ' + (case when @TESTSEGMENTID is null then 'null' else '@TESTSEGMENTID' end) + ',' + char(13) +
' cast([DONORS].[LISTDONORID] as varchar(36)),' + char(13) +
' [TEMPSOURCECODETABLE].[SOURCEQUERYVIEWID],' + char(13) +
' [TEMPSOURCECODETABLE].[SOURCECODE]' + char(13) +
' from @SEGMENTDONORS as [DONORS]' + char(13) +
' inner join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [DONORS].[LISTDONORID]' + char(13) +
' inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTLISTDATA].[SEGMENTLISTID]' + char(13) +
' inner join @CONSOLIDATEDSOURCECODETABLE as [TEMPSOURCECODETABLE] on [TEMPSOURCECODETABLE].[SOURCEQUERYVIEWID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]' + char(13) +
' where [DONORS].[LISTDONORID] is not null;';
exec sp_executesql @SQL, N'@MAXROWS int, @SEGMENTID uniqueidentifier, @TESTSEGMENTID uniqueidentifier, @SOURCEQUERYVIEWID uniqueidentifier, @IDFORBUILDSOURCECODE uniqueidentifier, @SOURCECODE nvarchar(50), @NTHRECORD decimal(20,5), @SEGMENTRECORDSOURCEID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @MKTSEGMENTID uniqueidentifier',
@MAXROWS = @MAXROWS,
@SEGMENTID = @SEGMENTID,
@TESTSEGMENTID = @TESTSEGMENTID,
@SOURCEQUERYVIEWID = @SOURCEQUERYVIEWID,
@IDFORBUILDSOURCECODE = @IDFORBUILDSOURCECODE,
@SOURCECODE = @SOURCECODE,
@NTHRECORD = @NTHRECORD,
@SEGMENTRECORDSOURCEID = @SEGMENTRECORDSOURCEID,
@SEGMENTATIONID = @SEGMENTATIONID,
@MKTSEGMENTID = @MKTSEGMENTID;
if @MAILINGTYPECODE in (1, 5)
set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_REVENUEID_DONORID';
else if @MAILINGTYPECODE = 2
set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_MEMBERSHIPID_DONORID';
else if @MAILINGTYPECODE = 3
set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_SPONSORSHIPID_DONORID';
else
set @INDEXNAME = 'IX_' + @DATATABLE + '_DONORQUERYVIEWCATALOGID_DONORID';
set @SQL = 'update statistics dbo.[' + @DATATABLE + '] [' + @INDEXNAME + ']';
exec (@SQL);
if @TESTSEGMENTID is null
begin
--We are done activating this segment and all its test segments, so drop the temp table...
exec ('drop table ' + @TEMPSEGMENTTABLENAME);
--Cache the record count for the segment
set @SQL = 'select @RECORDCOUNT = count(*) from dbo.[' + @DATATABLE + '] where [SEGMENTID] = @SEGMENTID';
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @RECORDCOUNT int output', @SEGMENTID = @SEGMENTID, @RECORDCOUNT = @RECORDCOUNT output;
if @MAILINGTYPECODE = 1 --Acknowledgment
--Since acknowledgments don't run the previous segment exclusions caching like normal segments, we
--need to manually add the cache record here so that other process later on can pick up the counts.
insert into dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] (
[SEGMENTID],
[RECORDCOUNT],
[OFFERCOUNT],
[RECORDCOUNTDATECACHED]
) values (
@SEGMENTID,
@RECORDCOUNT,
@RECORDCOUNT,
getdate()
);
else
update dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] set
[OFFERCOUNT] = @RECORDCOUNT,
[RECORDCOUNTDATECACHED] = getdate()
where [SEGMENTID] = @SEGMENTID;
end
else
begin
--Delete all the rows from the temp table that we inserted into the mailing data table, and then resequence
--the row numbers in the temp table so that activating the next test segment will work correctly.
set @SQL = 'drop index [IX_' + replace(@TEMPSEGMENTTABLENAME, '#', '') + '_ROW] on dbo.[' + @TEMPSEGMENTTABLENAME + '];' + char(13) +
'alter table dbo.[' + @TEMPSEGMENTTABLENAME + '] drop column [ROW];' + char(13) +
'delete dbo.[' + @TEMPSEGMENTTABLENAME + '] from dbo.[' + @TEMPSEGMENTTABLENAME + '] as [TEMPSEG] inner join dbo.[' + @TEMPNTHTABLENAME + '] as [TEMPTEST] on [TEMPTEST].[ID] = [TEMPSEG].[ID]' + (case when @MAILINGTYPECODE in (1, 5) and @ISBBEC = 1 then ' and [TEMPTEST].[DONORID] = [TEMPSEG].[DONORID]' else '' end) + ';' + char(13) +
'drop table dbo.[' + @TEMPNTHTABLENAME + '];' + char(13) +
'alter table dbo.[' + @TEMPSEGMENTTABLENAME + '] add [ROW] [int] not null identity (1, 1);' + char(13) +
'create unique nonclustered index [IX_' + replace(@TEMPSEGMENTTABLENAME, '#', '') + '_ROW] on [dbo].[' + @TEMPSEGMENTTABLENAME + '] ([ROW] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);';
exec (@SQL);
end
end
else -- @SEGMENTATIONISHISTORICAL = 1
begin
if @TESTSEGMENTID is null
insert into dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] (
[SEGMENTID],
[RECORDCOUNT],
[OFFERCOUNT],
[RECORDCOUNTDATECACHED]
)
select
@SEGMENTID,
(case when @MAXROWS > 0 then @MAXROWS else (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end) end),
(case when @MAXROWS > 0 then @MAXROWS else (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end) end),
getdate()
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
end
if @MAILINGTYPECODE = 3 and @ISACTIVATING = 1
begin
declare @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier;
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
select
@SPONSORSHIPMAILINGTEMPLATEID = [MKTSPONSORSHIPMAILINGPROCESS].[SPONSORSHIPMAILINGTEMPLATEID]
from dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION]
inner join dbo.[MKTSPONSORSHIPMAILINGPROCESSSTATUS] on [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SPONSORSHIPMAILINGPROCESSSTATUSID]
inner join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID]
where [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = @SEGMENTATIONID;
set @SQL = 'insert into dbo.[MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED] (' + char(13) +
' [MKTSEGMENTATIONID],' + char(13)+
' [SPONSORSHIPID],' + char(13) +
' [DATATABLE],' + char(13) +
' [MKTSPONSORSHIPMAILINGTEMPLATEID],' + char(13) +
' [MKTSEGMENTID],' + char(13) +
' [DATESENT],' + char(13) +
' [ADDEDBYID],' + char(13) +
' [CHANGEDBYID])' + char(13) +
'select' + char(13) +
' @SEGMENTATIONID,' + char(13) +
' [SPONSORSHIPID],' + char(13) +
' @DATATABLE,' + char(13) +
' @SPONSORSHIPMAILINGTEMPLATEID,' + char(13) +
' @SEGMENTID,' + char(13) +
' getdate(),' + char(13) +
' @CHANGEAGENTID,' + char(13) +
' @CHANGEAGENTID'+ char(13) +
'from dbo.[' + @DATATABLE + ']';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @DATATABLE nvarchar(510), @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier, @SEGMENTID uniqueidentifier, @CHANGEAGENTID uniqueidentifier',
@SEGMENTATIONID = @SEGMENTATIONID,
@DATATABLE = @DATATABLE,
@SPONSORSHIPMAILINGTEMPLATEID = @SPONSORSHIPMAILINGTEMPLATEID,
@SEGMENTID = @MKTSEGMENTID,
@CHANGEAGENTID = @CHANGEAGENTID;
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;