USP_MKTSEGMENTATIONACTIVATE_CACHECONSTITUENTSEGMENTS
Saves the activated marketing effort's constituent and segment relationships.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @SEGMENTATIONEXCLUSIONID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHECONSTITUENTSEGMENTS]
(
@SEGMENTATIONID uniqueidentifier,
@SEGMENTATIONEXCLUSIONID uniqueidentifier = null
)
as
set nocount on;
declare @ACTIVE bit;
declare @SEGMENTATIONISHISTORICAL bit;
declare @RECORDSOURCEID uniqueidentifier;
declare @RECORDTYPEID uniqueidentifier;
declare @CONSTITUENTSEGMENTTABLE nvarchar(128);
declare @SEGMENTID uniqueidentifier;
declare @TESTSEGMENTID uniqueidentifier;
declare @DATATABLE nvarchar(255);
declare @SQL nvarchar(max);
declare @MARKETINGRECORDTYPE tinyint; /* 1=Record Source, 2=List/Duplicate List, 3=Consolidated List */
declare @LISTID uniqueidentifier;
declare @ISVENDORMANAGED bit;
declare @DONORQUERYVIEWCATALOGID uniqueidentifier;
declare @SOURCECODEMAPID uniqueidentifier;
declare @LISTIDSQL nvarchar(128);
declare @WHERESQL nvarchar(max);
declare @MAILINGTYPECODE tinyint;
declare @LISTMATCHBACKTABLE nvarchar(128);
declare @FINDERFILEWITHSQL nvarchar(max) = '';
declare @FINDERFILETABLENAME nvarchar(128) = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@SEGMENTATIONID);
declare @FINDERFILETABLEEXISTS bit = (case when object_id(@FINDERFILETABLENAME, 'U') is not null then 1 else 0 end);
declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
declare @FINDERTABLEROWS bigint;
declare @DISCTINCTFINDERNUMBERS bigint;
declare @SINGLEFINDERFILE bit = 0;
if @FINDERFILETABLEEXISTS = 1 and @ISBBEC = 1
begin
-- BTR 8/13/2013 WI 302204 -- this only applies to BBEC, since only BBEC uses the CONSTITUENTSEGMENT_<GUID> table to shortcut finder number lookups
-- (it doesn't work in DM/RE because RE constituent IDs are integers, and thus you can't join <finder file table>.[ID] to <segmentation data>.[DONORID])
--MDC 2013-05-22 - WI274032 - For an effort that has an imported finder file, we need to grab the finder number if the constituent is in the finder file
--If they import multiple finder files and import the same FINDERNUMBER multiple times with different names, segments, or sourcecodes,
--then we need to make sure we only grab info for the findernumber from the latest import file.
--using the row_number() is same method used in USP_DATALIST_MKTFINDERNUMBERLOOKUP to determine the latest finder number
--For performance reasons, check if the finder numbers are distinct
set @SQL = 'select @FINDERTABLEROWS = count(1) from dbo.[' + @FINDERFILETABLENAME + '];'
exec sp_executesql @SQL, N'@FINDERTABLEROWS bigint output', @FINDERTABLEROWS = @FINDERTABLEROWS output;
set @SQL = 'select @DISCTINCTFINDERNUMBERS = count(distinct [FINDERNUMBER]) from dbo.[' + @FINDERFILETABLENAME + '];'
exec sp_executesql @SQL, N'@DISCTINCTFINDERNUMBERS bigint output', @DISCTINCTFINDERNUMBERS = @DISCTINCTFINDERNUMBERS output;
if @FINDERTABLEROWS <> @DISCTINCTFINDERNUMBERS
begin
--create a temp table to hold the ranked finder numbers so we only have to do this once
if object_id ('tempdb.dbo.#RANKEDFINDERNUMBERS') is not null
drop table #RANKEDFINDERNUMBERS;
create table #RANKEDFINDERNUMBERS ([ID] uniqueidentifier not null, [FINDERNUMBER] bigint);
set @FINDERFILEWITHSQL =
'with [FINDERNUMBERSINORDER] ([ID], [RANK], [FINDERNUMBER]) as (' + char(13) +
' select' + char(13) +
' [FT].[ID],' + char(13) +
' row_number() over (partition by [FT].[FINDERNUMBER] order by [MKTFINDERFILEIMPORTPROCESS].[DATEADDED] desc) as [RANK],' + char(13) +
' [FT].[FINDERNUMBER]' + char(13) +
' from dbo.[' + @FINDERFILETABLENAME + '] as [FT] with (index([IX_' + @FINDERFILETABLENAME + '_FINDERNUMBER]))' + char(13) +
' inner join dbo.[MKTFINDERFILEIMPORTPROCESS] on [MKTFINDERFILEIMPORTPROCESS].[ID] = [FT].[FINDERFILEID]' + char(13) +
')'+ char(13) +
'insert #RANKEDFINDERNUMBERS ([ID], [FINDERNUMBER])' + char(13) +
' select' + char(13) +
' [ID],' + char(13) +
' [FINDERNUMBER]' + char(13) +
' from [FINDERNUMBERSINORDER]' + char(13) +
' where [FINDERNUMBERSINORDER].[RANK] = 1;' + char(13);
exec sp_executesql @FINDERFILEWITHSQL;
--add index
alter table #RANKEDFINDERNUMBERS add constraint [PK_RANKEDFINDERNUMBERS_ID] primary key ([ID]);
end
else
set @SINGLEFINDERFILE = 1;
end
select
@MAILINGTYPECODE = [MAILINGTYPECODE],
@ACTIVE = [ACTIVE],
@SEGMENTATIONISHISTORICAL = [ISHISTORICAL],
@DATATABLE = (case when @SEGMENTATIONEXCLUSIONID is null then dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]) else dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([ID]) end)
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
if @SEGMENTATIONISHISTORICAL = 0
begin
/* Loop through each record source in this mailing because we have a separate CONSTITUENTSEGMENT table for each record source */
declare RECORDSOURCECURSOR cursor local fast_forward for
select [DRS].[QUERYVIEWCATALOGID], (case @MAILINGTYPECODE when 1 then [GQV].[RECORDTYPEID] when 2 then [MQV].[RECORDTYPEID] when 3 then [SQV].[RECORDTYPEID] when 5 then [GQV].[RECORDTYPEID] else [CQV].[RECORDTYPEID] end)
from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [DRS]
inner join dbo.[QUERYVIEWCATALOG] as [CQV] on [CQV].[ID] = [DRS].[QUERYVIEWCATALOGID]
inner join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [DRS].[QUERYVIEWCATALOGID]
left outer join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [DRS].[QUERYVIEWCATALOGID]
left outer join dbo.[MKTSPONSORSHIPRECORDSOURCE] on [MKTSPONSORSHIPRECORDSOURCE].[ID] = [DRS].[QUERYVIEWCATALOGID]
inner join dbo.[QUERYVIEWCATALOG] as [GQV] on [GQV].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
left outer join dbo.[QUERYVIEWCATALOG] as [MQV] on [MQV].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
left outer join dbo.[QUERYVIEWCATALOG] as [SQV] on [SQV].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID];
open RECORDSOURCECURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @RECORDTYPEID;
while (@@FETCH_STATUS = 0)
begin
/* Get the CONSTITUENTSEGMENT table name */
set @CONSTITUENTSEGMENTTABLE = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@RECORDSOURCEID);
set @LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME](@RECORDSOURCEID);
if @SEGMENTATIONEXCLUSIONID is null
begin
if @ACTIVE = 1
begin
/* Only remove the existing constituent IDs for this mailing where the segment uses a list or consolidated list. */
/* We do this so we don't have to delete and re-insert the same house file constit IDs every time, since the constit */
/* IDs will not change except for lists because we run matchback. */
set @SQL = 'delete from dbo.[' + @CONSTITUENTSEGMENTTABLE + ']' + char(13) +
'where [SEGMENTID] in (' + char(13) +
' select [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
' from dbo.[MKTSEGMENTATIONSEGMENT]' + char(13) +
' inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
' left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]' + char(13) +
' where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
' and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0' + char(13) +
' and ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 or [IDSETREGISTER].[RECORDTYPEID] <> @RECORDTYPEID))';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @RECORDTYPEID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @RECORDTYPEID = @RECORDTYPEID;
end
else
begin
/* Remove all the existing constituent IDs for this mailing... */
set @SQL = 'delete from dbo.[' + @CONSTITUENTSEGMENTTABLE + ']' + char(13) +
'where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [EXCLUDE] = 0)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
end
/* Loop through each segment and test segment in the mailing for this record source and insert the */
/* constituent IDs into the CONSTITUENTSEGMENT table. Only grab the segments that use this record source. */
if @SEGMENTATIONEXCLUSIONID is null
declare SEGMENTCURSOR cursor local fast_forward for
select [MKTSEGMENTATIONSEGMENT].[ID], null
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
and (@ACTIVE = 0 or (@ACTIVE = 1 and ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 or [IDSETREGISTER].[RECORDTYPEID] <> @RECORDTYPEID)))
union all
select[MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENTATIONTESTSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
and [MKTSEGMENT].[QUERYVIEWCATALOGID] = @RECORDSOURCEID
and (@ACTIVE = 0 or (@ACTIVE = 1 and ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 or [IDSETREGISTER].[RECORDTYPEID] <> @RECORDTYPEID)));
else
begin
declare @SEGMENTTABLE table ([SEGMENTID] uniqueidentifier, [TESTSEGMENTID] uniqueidentifier);
set @SQL = 'select distinct [SEGMENTID], [TESTSEGMENTID]' + char(13) +
'from dbo.[' + @DATATABLE + ']' + char(13) +
'where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
insert into @SEGMENTTABLE
exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;
declare SEGMENTCURSOR cursor local fast_forward for
select [S].[SEGMENTID], [S].[TESTSEGMENTID]
from @SEGMENTTABLE as [S]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [S].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0;
end
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
while (@@FETCH_STATUS = 0)
begin
if @TESTSEGMENTID is null
begin
-- Segment
select
@SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONSEGMENTID] = @SEGMENTID and [SEGMENTATIONTESTSEGMENTID] is null;
end
else
begin
-- Test segment
select
@SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONSEGMENTID] = @SEGMENTID and [SEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID;
end
/* Gather some info so we can build the SQL */
select distinct
@LISTID = [MKTSEGMENTLIST].[LISTID],
@ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]),
@MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
@DONORQUERYVIEWCATALOGID = (case when dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) = 2 then [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] else @RECORDSOURCEID end)
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.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
and (([MKTSEGMENTLIST].[LISTID] is not null and [MKTSOURCECODEMAP].[ID] is null) or ([MKTSEGMENTLIST].[LISTID] is null and [MKTSOURCECODEMAP].[LISTID] is null) or ([MKTSOURCECODEMAP].[LISTID] = (case when dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) = 2 then [MKTSEGMENTLIST].[LISTID] else @RECORDSOURCEID end)));
set @SQL = 'insert into dbo.[' + @CONSTITUENTSEGMENTTABLE +'] ([CONSTITUENTID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID], [FINDERNUMBER])' + char(13);
if @MARKETINGRECORDTYPE = 2 or @MARKETINGRECORDTYPE = 3 --List/Duplicate List or Consolidated List
/* See if we need to cast the person ID from the list matchback table(s) in the list joins... */
select @LISTIDSQL = (case when [DATA_TYPE] = 'uniqueidentifier' then '[LISTDONORS].[ID]' else 'cast(isnull([LISTDONORS].[ID],'''') as varchar(36))' end)
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_SCHEMA] = 'dbo'
and [TABLE_NAME] = @DATATABLE
and [COLUMN_NAME] = 'DONORID';
if @MARKETINGRECORDTYPE = 1 or @MARKETINGRECORDTYPE = 2 --Record Source or List/Duplicate List
begin
if @MARKETINGRECORDTYPE = 1 --Record Source
/* Select directly from the activated data table */
set @SQL += ' select distinct [DONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID, [DONORS].[FINDERNUMBER]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13);
else --List/Duplicate List
begin
/* Join to the matchback table for this list. */
set @SQL += ' select distinct [LISTDONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID, ';
if @FINDERFILETABLEEXISTS = 1 and @ISBBEC = 1
set @SQL += 'isnull([MOSTRECENTFINDERNUMBER].[FINDERNUMBER], (case when [DONORS].[FINDERNUMBER] > 0 then [DONORS].[FINDERNUMBER] else 0 end))' + char(13);
else
set @SQL += '(case when [DONORS].[FINDERNUMBER] > 0 then [DONORS].[FINDERNUMBER] else 0 end)' + char(13);
set @SQL += ' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
' inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13);
end
if @SEGMENTATIONEXCLUSIONID is not null
set @SQL += ' left join dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA] on [DONORS].[FINDERNUMBER] = [DATA].[FINDERNUMBER]';
if @FINDERFILETABLEEXISTS = 1 and @ISBBEC = 1
begin
if @SINGLEFINDERFILE = 0
set @SQL += ' left join #RANKEDFINDERNUMBERS as [MOSTRECENTFINDERNUMBER] on [LISTDONORS].[DONORID] = [MOSTRECENTFINDERNUMBER].[ID]' + char(13);
else
set @SQL += ' left join dbo.[' + @FINDERFILETABLENAME +'] as [MOSTRECENTFINDERNUMBER] with (index([IX_' + @FINDERFILETABLENAME + '_FINDERNUMBER])) on [LISTDONORS].[DONORID] = [MOSTRECENTFINDERNUMBER].[ID]' + char(13);
end
set @SQL += ' where [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
' and [DONORS].[DONORQUERYVIEWCATALOGID] ' + (case when @ISVENDORMANAGED = 1 or @DONORQUERYVIEWCATALOGID is null then 'is null' else '= @DONORQUERYVIEWCATALOGID' end) + char(13) +
(case when @SEGMENTATIONEXCLUSIONID is null then '' else ' and [DONORS].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID and [DATA].[FINDERNUMBER] is null' end);
end
else
begin --Consolidated List
set @WHERESQL = ' where [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) +
(case when @SEGMENTATIONEXCLUSIONID is null then '' else char(13) + ' and [DONORS].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID and [DATA].[FINDERNUMBER] is null' end);
/* 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 = 'with [LISTDONORS]([ID], [DONORID], [DONORQUERYVIEWCATALOGID], [SOURCECODEMAPID]) as (' + char(13) +
' select distinct' + char(13) +
' [LISTMATCHBACK].[ID],' + char(13) +
' [LISTMATCHBACK].[DONORID],' + char(13) +
' [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] as [DONORQUERYVIEWCATALOGID],' + char(13) +
' (select [MKTSOURCECODEMAP].[ID]' + char(13) +
' from dbo.[MKTSOURCECODEMAP]' + char(13) +
' where [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
' and ((@TESTSEGMENTID is null and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null) or ([MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID))' + 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) +
@SQL +
' select distinct [LISTDONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, [LISTDONORS].[SOURCECODEMAPID], (case when [DONORS].[FINDERNUMBER] > 0 then [DONORS].[FINDERNUMBER] else 0 end)' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
' inner join [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID]' + char(13);
if @SEGMENTATIONEXCLUSIONID is not null
set @SQL += ' left join dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA] on [DONORS].[FINDERNUMBER] = [DATA].[FINDERNUMBER]' + char(13);
set @SQL += @WHERESQL + char(13) +
' and [DONORS].[DONORQUERYVIEWCATALOGID] in (select distinct [DONORQUERYVIEWCATALOGID] from [LISTDONORS])' + char(13) +
char(13) +
' union all' + char(13) +
char(13) +
' select distinct [DONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID, [DONORS].[FINDERNUMBER]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13);
if @SEGMENTATIONEXCLUSIONID is not null
set @SQL += ' left join dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA] on [DONORS].[FINDERNUMBER] = [DATA].[FINDERNUMBER]' + char(13);
set @SQL += @WHERESQL + char(13) +
' and [DONORS].[DONORQUERYVIEWCATALOGID] = @DONORQUERYVIEWCATALOGID' + char(13) +
' and (not exists(select * from [LISTDONORS]) or [DONORS].[DONORID] not in (select ' + @LISTIDSQL + ' from [LISTDONORS]))';
end
/* Insert the constituent, segment, test segment ID, and list source code part IDs into the CONSTITUENTSEGMENT table */
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @SEGMENTID uniqueidentifier, @TESTSEGMENTID uniqueidentifier, @DONORQUERYVIEWCATALOGID uniqueidentifier, @SOURCECODEMAPID uniqueidentifier, @SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @SEGMENTID = @SEGMENTID, @TESTSEGMENTID = @TESTSEGMENTID, @DONORQUERYVIEWCATALOGID = @DONORQUERYVIEWCATALOGID, @SOURCECODEMAPID = @SOURCECODEMAPID, @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
end
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @RECORDTYPEID;
end
close RECORDSOURCECURSOR;
deallocate RECORDSOURCECURSOR;
end
if object_id ('tempdb.dbo.#RANKEDFINDERNUMBERS') is not null
drop table #RANKEDFINDERNUMBERS;
return 0;