USP_MKTSEGMENTATIONACTIVATE_CACHEREVENUESEGMENTS
Saves the activated marketing effort's revenue and segment relationships.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEREVENUESEGMENTS]
(
@SEGMENTATIONID uniqueidentifier
)
as
set nocount on;
declare @DATATABLE nvarchar(128);
declare @MAILINGTYPECODE tinyint;
declare @SOURCECODEID uniqueidentifier;
declare @DONORMATCHFIELD nvarchar(20);
declare @RECORDSOURCEID uniqueidentifier;
declare @GIFTIDSETNAME nvarchar(128);
declare @REVENUESEGMENTTABLE nvarchar(128);
declare @LISTMATCHBACKTABLE nvarchar(128);
declare @REVENUEDATATYPENAME nvarchar(20);
declare @SEGMENTID uniqueidentifier;
declare @TESTSEGMENTID uniqueidentifier;
declare @RECORDSOURCETYPE tinyint; /* 1=Record Source, 2=List/Duplicate List, 3=Consolidated List */
declare @LISTID uniqueidentifier;
declare @DONORQUERYVIEWID uniqueidentifier;
declare @SOURCECODEMAPID uniqueidentifier;
declare @SEGMENTSOURCECODE nvarchar(50);
declare @ISFIRSTSEGMENTWITHTHISSOURCECODE bit;
declare @NEEDCAST bit;
declare @WITHBLOCKSQL nvarchar(max);
declare @SQL nvarchar(max);
declare @WHERESQL nvarchar(max);
declare @PARAMDEF nvarchar(max);
begin try
select
@DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]),
@MAILINGTYPECODE = [MAILINGTYPECODE],
@SOURCECODEID = [SOURCECODEID]
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
if @MAILINGTYPECODE in (1, 2, 3) -- acknowledgment, membership, or sponsorship mailings
--For acknowledgment/membership/sponsorship mailings, the same donor could be in the mailing more than once because he is receiving an
--acknowledgment for multiple gifts, or being mailed as a result of multiple memberships, or multiple sponsorships.
--Joining on DONORID causes duplicate records in the counts, so join on the FINDERNUMBER field instead.
set @DONORMATCHFIELD = 'FINDERNUMBER';
else
set @DONORMATCHFIELD = 'DONORID';
--Create a temp table of all segment IDs in the mailing...
create table #MAILINGSEGMENTS (
[ID] uniqueidentifier not null primary key
);
--Create a table to use for matching gifts based on sourcecode...
if @SOURCECODEID is not null
begin
create table #MAILINGSOURCECODES (
[SOURCECODE] nvarchar(50) collate database_default not null,
[SEGMENTID] uniqueidentifier not null,
[TESTSEGMENTID] uniqueidentifier,
[SEGMENTSEQUENCE] int not null,
[TESTSEGMENTSEQUENCE] int not null
);
create clustered index [IX_MAILINGSOURCECODES_SOURCECODE] on #MAILINGSOURCECODES ([SOURCECODE]);
end
/* Loop through each record source in this mailing because we have a separate REVENUESEGMENT table for each record source */
declare RECORDSOURCECURSOR cursor local fast_forward for
select
[RS].[QUERYVIEWCATALOGID],
dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]),
dbo.[UFN_REVENUESEGMENT_MAKETABLENAME]([RS].[QUERYVIEWCATALOGID]),
dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([RS].[QUERYVIEWCATALOGID]),
[QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME]
from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [RS].[QUERYVIEWCATALOGID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [RS].[QUERYVIEWCATALOGID];
open RECORDSOURCECURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @GIFTIDSETNAME, @REVENUESEGMENTTABLE, @LISTMATCHBACKTABLE, @REVENUEDATATYPENAME;
while (@@FETCH_STATUS = 0)
begin
--Create a temp table to hold the results for each segment. We'll then merge this temp table into the REVENUESEGMENT table for each record source...
create table #REVSEGTEMP (
/*[REVENUEID] column will be added below dynamically*/
[SEGMENTID] uniqueidentifier not null,
[TESTSEGMENTID] uniqueidentifier,
[SOURCECODEMAPID] uniqueidentifier
);
--Alter the temp table and add the REVENUEID column with the correct datatype. We do this because we can't create the
--temp table in dynamic sql because it will not stay in scope afterwards.
set @SQL = 'alter table #REVSEGTEMP add [REVENUEID] ' + @REVENUEDATATYPENAME + ' not null primary key';
exec (@SQL);
--Grab all the segments for this mailing and record source combination...
truncate table #MAILINGSEGMENTS;
insert into #MAILINGSEGMENTS ([ID])
select [MKTSEGMENTATIONSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID;
--Grab all the sourcecodes for this mailing and record source combination so we can match gifts based on sourcecode too...
if @SOURCECODEID is not null
begin
truncate table #MAILINGSOURCECODES;
insert into #MAILINGSOURCECODES ([SOURCECODE], [SEGMENTID], [TESTSEGMENTID], [SEGMENTSEQUENCE], [TESTSEGMENTSEQUENCE])
select
[MKTSOURCECODEMAP].[SOURCECODE],
[MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] as [SEGMENTID],
[MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] as [TESTSEGMENTID],
[MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE],
isnull([MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE], 0) as [TESTSEGMENTSEQUENCE]
from dbo.[MKTSOURCECODEMAP]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]
left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID]
where [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSOURCECODEMAP].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID;
end
--See if we'll need to cast the person ID...
select
@NEEDCAST = (case when [DATA_TYPE] = 'uniqueidentifier' then 0 else 1 end)
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_SCHEMA] = 'dbo'
and [TABLE_NAME] = @DATATABLE
and [COLUMN_NAME] = 'DONORID';
--For debug only...
--declare @COUNT int = 0;
--declare @TOTAL int;
--declare @STATUS nvarchar(100);
--set @TOTAL = (
-- (select count(*)
-- from dbo.[MKTSEGMENTATIONSEGMENT]
-- inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
-- where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
-- and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID)
-- +
-- (select count(*)
-- from dbo.[MKTSEGMENTATIONSEGMENT]
-- inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
-- inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
-- where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
-- and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID)
--);
/* Loop through each segment and test segment in the mailing for this record source and insert the */
/* revenue IDs into the REVENUESEGMENT table. Only grab the segments that use this record source */
/* and make sure we process them in top-down order. */
declare SEGMENTCURSOR cursor local fast_forward for
select T.[SEGMENTID], T.[TESTSEGMENTID]
from (
select
[MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
null as [TESTSEGMENTID],
[MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE],
null as [TESTSEGMENTSEQUENCE]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
union all
select
[MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
[MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID],
[MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE],
[MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] as [TESTSEGMENTSEQUENCE]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
) as T
order by T.[SEGMENTSEQUENCE], T.[TESTSEGMENTSEQUENCE];
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
while (@@FETCH_STATUS = 0)
begin
--For debug only...
--set @COUNT += 1;
--set @STATUS = 'Calculating segment ' + cast(@COUNT as nvarchar(10)) + ' of ' + cast(@TOTAL as nvarchar(10)) + '...';
--raiserror(@STATUS, 0, 0) with nowait;
/* Gather some info so we can build the SQL */
if @TESTSEGMENTID is not null
select distinct
@RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
@LISTID = [MKTSEGMENTLIST].[LISTID],
@DONORQUERYVIEWID = (case when [MKTSEGMENTLIST].[ID] is not null then isnull([MKTSEGMENTLIST].[QUERYVIEWCATALOGID], [MKTSEGMENTLIST_PARENT].[QUERYVIEWCATALOGID]) else @RECORDSOURCEID end),
@SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
@SEGMENTSOURCECODE = [MKTSOURCECODEMAP].[SOURCECODE]
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
left join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLIST_PARENT] on [MKTSEGMENTLIST_PARENT].[SEGMENTID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID] and [MKTSOURCECODEMAP].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID and ([MKTSEGMENTLIST].[LISTID] is null or [MKTSOURCECODEMAP].[LISTID] = [MKTSEGMENTLIST].[LISTID])
where [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = @SEGMENTID
and [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
else
select distinct
@RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
@LISTID = [MKTSEGMENTLIST].[LISTID],
@DONORQUERYVIEWID = (case when [MKTSEGMENTLIST].[ID] is not null then isnull([MKTSEGMENTLIST].[QUERYVIEWCATALOGID], [MKTSEGMENTLIST_PARENT].[QUERYVIEWCATALOGID]) else @RECORDSOURCEID end),
@SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
@SEGMENTSOURCECODE = [MKTSOURCECODEMAP].[SOURCECODE]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
left join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLIST_PARENT] on [MKTSEGMENTLIST_PARENT].[SEGMENTID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null and [MKTSOURCECODEMAP].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID and ([MKTSEGMENTLIST].[LISTID] is null or [MKTSOURCECODEMAP].[LISTID] = [MKTSEGMENTLIST].[LISTID])
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
--Check if the sourcecode matches this segment/test segment and it is the first one in the mailing (top-down) if the mailing contains duplicate sourecodes.
if @SOURCECODEID is not null
begin
if exists(select *
from (
select top(1) [SEGMENTID], [TESTSEGMENTID]
from #MAILINGSOURCECODES
where [SOURCECODE] = @SEGMENTSOURCECODE
order by [SEGMENTSEQUENCE], [TESTSEGMENTSEQUENCE]
) as T
where T.[SEGMENTID] = @SEGMENTID
and ((@TESTSEGMENTID is null and T.[TESTSEGMENTID] is null) or T.[TESTSEGMENTID] = @TESTSEGMENTID))
set @ISFIRSTSEGMENTWITHTHISSOURCECODE = 1;
else
set @ISFIRSTSEGMENTWITHTHISSOURCECODE = 0;
end
else
set @ISFIRSTSEGMENTWITHTHISSOURCECODE = 0;
set @WITHBLOCKSQL = 'with [SEGMENTDONORS] ([' + @DONORMATCHFIELD + ']' + (case when @RECORDSOURCETYPE = 2 then ', [GIFTID]' else '' end) + ') as' + char(13) +
'(' + char(13) +
' select ' + (case when @RECORDSOURCETYPE = 2 then '[LISTDONORS].[DONORID], [LISTDONORS].[GIFTID]' else '[DONORS].[' + @DONORMATCHFIELD + ']' end) + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
(case when @RECORDSOURCETYPE = 2 then ' inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[ID] as nvarchar(36))' else '[LISTDONORS].[ID]' end) + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) else '' end) +
' where [DONORS].[DONORQUERYVIEWCATALOGID] ' + (case when @DONORQUERYVIEWID is null then 'is null' else '= @DONORQUERYVIEWID' end) + char(13) +
' and [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
' and [DONORS].[FINDERNUMBER] ' + (case when @DONORQUERYVIEWID is null then '< 0' else '> 0' end) + char(13) +
')';
if @RECORDSOURCETYPE in (1, 2) --Record Source or List/Duplicate List
begin
set @SQL = @WITHBLOCKSQL + char(13) +
'insert into #REVSEGTEMP ([REVENUEID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13) +
' select distinct [GIFTIDSET].[ID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
' from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + char(13) +
' inner join [SEGMENTDONORS] on [SEGMENTDONORS].[' + @DONORMATCHFIELD + '] = [GIFTIDSET].[' + @DONORMATCHFIELD + ']' + (case when @RECORDSOURCETYPE = 2 then ' and [SEGMENTDONORS].[GIFTID] = [GIFTIDSET].[ID]' else '' end) + char(13) +
' where not exists(select * from #REVSEGTEMP where [REVENUEID] = [GIFTIDSET].[ID])' + char(13) +
' option (optimize for (@SEGMENTID unknown, @TESTSEGMENTID unknown));';
if @ISFIRSTSEGMENTWITHTHISSOURCECODE = 1
--If we have the first segment in the mailing with this sourcecode (mailings could have duplicate sourcecodes), then...
-- * Count gift if source code matches, and...
-- * If the DONORID will not be matched to any other segment above or below this one...
set @SQL += char(13) + char(13) +
'with [NONSEGMENTDONORS] ([' + @DONORMATCHFIELD + ']) as' + char(13) +
'(' + char(13) +
' select ' + (case when @RECORDSOURCETYPE = 2 then '[LISTDONORS].[DONORID]' else '[DONORS].[' + @DONORMATCHFIELD + ']' end) + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
(case when @RECORDSOURCETYPE = 2 then ' inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[ID] as nvarchar(36))' else '[LISTDONORS].[ID]' end) + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) else '' end) +
' where [DONORS].[DONORQUERYVIEWCATALOGID] ' + (case when @DONORQUERYVIEWID is null then 'is null' else '= @DONORQUERYVIEWID' end) + char(13) +
' and not ([DONORS].[SEGMENTID] = @SEGMENTID and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')' + char(13) +
' and [DONORS].[FINDERNUMBER] ' + (case when @DONORQUERYVIEWID is null then '< 0' else '> 0' end) + char(13) +
')' + char(13) +
'insert into #REVSEGTEMP ([REVENUEID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13) +
' select [GIFTIDSET].[ID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
' from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + char(13) +
' where not exists(select * from #REVSEGTEMP where [REVENUEID] = [GIFTIDSET].[ID])' + char(13) +
' and [GIFTIDSET].[SOURCECODE] = @SEGMENTSOURCECODE' + char(13) +
' and not exists(select * from [NONSEGMENTDONORS] where [' + @DONORMATCHFIELD + '] = [GIFTIDSET].[' + @DONORMATCHFIELD + '])' + char(13) +
' option (optimize for (@SEGMENTID unknown, @TESTSEGMENTID unknown));';
end
else --Consolidated List
begin
/* Select the counts from the list matchback table first, then union to the counts from the record source. */
/* Performing these as separate 'select' statements and unioning them together increases performance dramatically. */
set @SQL = @WITHBLOCKSQL + ',' + char(13) +
'[LISTDONORS]([ID], [DONORID], [GIFTID], [SOURCECODEMAPID]) as' + char(13) +
'(' + char(13) +
' select distinct' + char(13) +
' [LISTMATCHBACK].[ID],' + char(13) +
' [LISTMATCHBACK].[DONORID],' + char(13) +
' [LISTMATCHBACK].[GIFTID],' + char(13) +
' (select [MKTSOURCECODEMAP].[ID]' + char(13) +
' from dbo.[MKTSOURCECODEMAP]' + char(13) +
' where [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
' and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
' and [MKTSOURCECODEMAP].[LISTID] = [MKTSEGMENTLIST].[LISTID]) as [SOURCECODEMAPID]' + char(13) +
' from dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTMATCHBACK]' + char(13) +
' inner join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [LISTMATCHBACK].[ID]' + char(13) +
' inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTLISTDATA].[SEGMENTLISTID]' + char(13) +
' where [LISTMATCHBACK].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
')' + char(13) +
'insert into #REVSEGTEMP ([REVENUEID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13) +
' select [GIFTIDSET].[ID], @SEGMENTID, @TESTSEGMENTID, [LISTDONORS].[SOURCECODEMAPID]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
' inner join [LISTDONORS] on ' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[ID] as nvarchar(36))' else '[LISTDONORS].[ID]' end) + ' = [DONORS].[DONORID]' + char(13) +
' inner join dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET] on [GIFTIDSET].[ID] = [LISTDONORS].[GIFTID]' + char(13) +
' where not exists(select * from #REVSEGTEMP where [REVENUEID] = [GIFTIDSET].[ID])' + char(13) +
' and [DONORS].[DONORQUERYVIEWCATALOGID] <> @DONORQUERYVIEWID' + char(13) +
' and [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
' and [DONORS].[FINDERNUMBER] > 0' + char(13) +
char(13) +
' union all' + char(13) +
char(13) +
' select [GIFTIDSET].[ID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
' from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + char(13) +
' inner join [SEGMENTDONORS] on [SEGMENTDONORS].[DONORID] = ' + (case when @NEEDCAST = 1 then 'cast([GIFTIDSET].[DONORID] as varchar(36))' else '[GIFTIDSET].[DONORID]' end) + char(13) +
' where not exists(select * from #REVSEGTEMP where [REVENUEID] = [GIFTIDSET].[ID])' + char(13) +
' and (not exists(select * from [LISTDONORS]) or [SEGMENTDONORS].[DONORID] not in (select ' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[DONORID] as nvarchar(36))' else '[LISTDONORS].[DONORID]' end) + ' from [LISTDONORS]))' + char(13) +
' option (optimize for (@SEGMENTID unknown, @TESTSEGMENTID unknown));';
if @ISFIRSTSEGMENTWITHTHISSOURCECODE = 1
--If we have the first segment in the mailing with this sourcecode (mailings could have duplicate sourcecodes), then...
-- * Count gift if source code matches, and...
-- * If the DONORID will not be matched to any other segment above or below this one...
set @SQL += char(13) + char(13) +
'declare @CONSOLIDATEDSOURCECODES table ([ID] uniqueidentifier not null primary key, [SOURCECODE] nvarchar(50) not null);' + char(13) +
'declare @CONSOLIDATEDQUERYVIEWS table ([ID] uniqueidentifier not null primary key);' + char(13) +
char(13) +
'insert into @CONSOLIDATEDSOURCECODES ([ID], [SOURCECODE])' + char(13) +
' select [ID], [SOURCECODE]' + char(13) +
' from dbo.[MKTSOURCECODEMAP]' + char(13) +
' where [SEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
' and [SEGMENTATIONTESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ';' + char(13) +
char(13) +
'insert into @CONSOLIDATEDQUERYVIEWS ([ID])' + char(13) +
' select distinct [DONORQUERYVIEWCATALOGID]' + char(13) +
' from dbo.[' + @DATATABLE + ']' + char(13) +
' where [SEGMENTID] = @SEGMENTID' + char(13) +
' and [TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ';' + char(13) +
char(13) +
'with [NONSEGMENTDONORS] ([DONORID]) as' + char(13) +
'(' + char(13) +
' select isnull(' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[DONORID] as nvarchar(36))' else '[LISTDONORS].[DONORID]' end) + ', [DONORS].[DONORID])' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
' left join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + (case when @NEEDCAST = 1 then 'cast([LISTDONORS].[ID] as nvarchar(36))' else '[LISTDONORS].[ID]' end) + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
' where [DONORS].[DONORQUERYVIEWCATALOGID] in (select [ID] from @CONSOLIDATEDQUERYVIEWS)' + char(13) +
' and not ([DONORS].[SEGMENTID] = @SEGMENTID and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')' + char(13) +
' and [DONORS].[FINDERNUMBER] > 0' + char(13) +
')' + char(13) +
'insert into #REVSEGTEMP ([REVENUEID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13) +
' select [GIFTIDSET].[ID], @SEGMENTID, @TESTSEGMENTID, [CONSOLSC].[ID]' + char(13) +
' from dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET]' + char(13) +
' inner join @CONSOLIDATEDSOURCECODES as [CONSOLSC] on [CONSOLSC].[SOURCECODE] = [GIFTIDSET].[SOURCECODE]' + char(13) +
' where not exists(select * from #REVSEGTEMP where [REVENUEID] = [GIFTIDSET].[ID])' + char(13) +
' and not exists(select * from [NONSEGMENTDONORS] where [DONORID] = ' + (case when @NEEDCAST = 1 then 'cast([GIFTIDSET].[DONORID] as nvarchar(36))' else '[GIFTIDSET].[DONORID]' end) + ')' + char(13) +
' option (optimize for (@SEGMENTID unknown, @TESTSEGMENTID unknown));';
end
set @PARAMDEF = '@SEGMENTATIONID uniqueidentifier, ' +
'@SEGMENTID uniqueidentifier, ' +
'@TESTSEGMENTID uniqueidentifier, ' +
'@SOURCECODEMAPID uniqueidentifier, ' +
'@DONORQUERYVIEWID uniqueidentifier, ' +
'@SEGMENTSOURCECODE nvarchar(50)';
exec sp_executesql @SQL, @PARAMDEF,
@SEGMENTATIONID = @SEGMENTATIONID,
@SEGMENTID = @SEGMENTID,
@TESTSEGMENTID = @TESTSEGMENTID,
@SOURCECODEMAPID = @SOURCECODEMAPID,
@DONORQUERYVIEWID = @DONORQUERYVIEWID,
@SEGMENTSOURCECODE = @SEGMENTSOURCECODE;
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
end
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
--For debug only...
--set @STATUS = 'Merging results into ' + @REVENUESEGMENTTABLE + '...';
--raiserror(@STATUS, 0, 0) with nowait;
--Now merge our temp table into the REVENUESEGMENT table...
-- * This will "update" the segment info for any existing revenue IDs that we calculated above (even if the revenue was previously for a different mailing).
-- * This will "insert" any new revenue IDs that don't already exist in the REVENUESEGMENT table.
-- * This will "delete" any leftover existing revenue IDs that were previously matched to this mailing.
set @SQL = 'merge into dbo.[' + @REVENUESEGMENTTABLE + '] t' + char(13) +
'using #REVSEGTEMP s on s.[REVENUEID] = t.[REVENUEID]' + char(13) +
'when matched and (s.[SEGMENTID] <> t.[SEGMENTID] or ((s.[TESTSEGMENTID] is null and t.[TESTSEGMENTID] is not null) or (s.[TESTSEGMENTID] is not null and t.[TESTSEGMENTID] is null) or s.[TESTSEGMENTID] <> t.[TESTSEGMENTID]) or ((s.[SOURCECODEMAPID] is null and t.[SOURCECODEMAPID] is not null) or (s.[SOURCECODEMAPID] is not null and t.[SOURCECODEMAPID] is null) or s.[SOURCECODEMAPID] <> t.[SOURCECODEMAPID])) then' + char(13) +
' update set [SEGMENTID] = s.[SEGMENTID], [TESTSEGMENTID] = s.[TESTSEGMENTID], [SOURCECODEMAPID] = s.[SOURCECODEMAPID]' + char(13) +
'when not matched then' + char(13) +
' insert ([REVENUEID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13) +
' values (s.[REVENUEID], s.[SEGMENTID], s.[TESTSEGMENTID], s.[SOURCECODEMAPID])' + char(13) +
'when not matched by source and t.[SEGMENTID] in (select [ID] from #MAILINGSEGMENTS) then' + char(13) +
' delete;';
exec (@SQL);
--Cleanup temp tables for the record source...
drop table #REVSEGTEMP;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @GIFTIDSETNAME, @REVENUESEGMENTTABLE, @LISTMATCHBACKTABLE, @REVENUEDATATYPENAME;
end
close RECORDSOURCECURSOR;
deallocate RECORDSOURCECURSOR;
--Cleanup temp tables for the mailing...
drop table #MAILINGSEGMENTS;
if @SOURCECODEID is not null
drop table #MAILINGSOURCECODES;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
if object_id('tempdb..#REVSEGTEMP') is not null
drop table #REVSEGTEMP;
if object_id('tempdb..#MAILINGSEGMENTS') is not null
drop table #MAILINGSEGMENTS;
if object_id('tempdb..#MAILINGSOURCECODES') is not null
drop table #MAILINGSOURCECODES;
return 1;
end catch
return 0;