USP_DATALIST_SEGMENTRETENTION
Retrieves a list of years and their retention for a given segment
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTID | uniqueidentifier | IN | Segment ID |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @NUMYEARS | int | IN |
Definition
Copy
CREATE procedure dbo.[USP_DATALIST_SEGMENTRETENTION]
(
@SEGMENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@NUMYEARS int = 5
)
with execute as owner
as
set nocount on;
declare @SEGMENTNAME nvarchar(100);
declare @SEGMENTIDSETREGISTERID uniqueidentifier;
declare @RECORDTYPEID uniqueidentifier;
declare @MARKETINGRECORDTYPE tinyint;
declare @MAILINGSTABLE table([ID] uniqueidentifier not null, [DATATABLE] nvarchar(128) not null, [MAILINGSEGMENTID] uniqueidentifier not null)
declare @MAILINGSCOUNT int;
declare @LISTMATCHBACKTABLE nvarchar(128);
declare @DATATABLE nvarchar(128);
declare @MAILINGSEGMENTID uniqueidentifier;
declare @GIFTIDSETNAME nvarchar(128);
declare @SQL nvarchar(max);
declare @PARAMETERS nvarchar(max);
declare @PRESQL nvarchar(max);
declare @INNERDONORSQL nvarchar(max);
declare @INNERLISTSQL nvarchar(max);
declare @INNERGIFTSQL nvarchar(max);
declare @DONORSCOUNT int;
declare @MATCHBACKFIELD nvarchar(20);
select
@SEGMENTNAME = [NAME],
@SEGMENTIDSETREGISTERID = [IDSETREGISTERID],
@RECORDTYPEID = dbo.[UFN_MKTSEGMENT_GETRECORDTYPE]([ID]),
@MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([ID]),
@MATCHBACKFIELD = (case when [MKTSEGMENT].[SEGMENTTYPECODE] = 3 then '[FINDERNUMBER]' else '[DONORID]' end) --Revenue segments can only be used in acknowledgement mailings which require finder number instead of donor ID for matchback.
from dbo.[MKTSEGMENT]
where [ID] = @SEGMENTID;
insert into @MAILINGSTABLE
select distinct
[MKTSEGMENTATION].[ID],
dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID]),
[MKTSEGMENTATIONSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))
and [MKTSEGMENTATION].[ACTIVE] = 1
and [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID;
-- check if the segment exists, if not, don't execute any code below, just return nothing
if exists(select top 1 1 from @MAILINGSTABLE)
begin
select @MAILINGSCOUNT = count([ID])
from @MAILINGSTABLE;
-- get the total count for the segment
set @SQL = 'select @DONORSCOUNT = count(*) from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SEGMENTIDSETREGISTERID);
exec sp_executesql @SQL, N'@DONORSCOUNT int output', @DONORSCOUNT = @DONORSCOUNT output;
set @SQL = 'select' + char(13) +
' year([GIFTIDSETS].[DATE]) as [YEAR],' + char(13) +
' count(distinct [GIFTIDSETS].[DONORID]) as [DONORCOUNT],' + char(13) +
' @DONORSCOUNT as [TOTALDONORS],' + char(13) +
' @SEGMENTNAME as [SEGMENTNAME]' + char(13);
set @PARAMETERS = '@DONORSCOUNT int, @SEGMENTNAME nvarchar(100)';
-- loop through all the activated mailings that use the segment and join to each mailing's activated data table
declare MAILINGCURSOR cursor local fast_forward for
select [DATATABLE], [MAILINGSEGMENTID]
from @MAILINGSTABLE;
open MAILINGCURSOR;
fetch next from MAILINGCURSOR into @DATATABLE, @MAILINGSEGMENTID;
while (@@FETCH_STATUS = 0)
begin
if @INNERDONORSQL is not null
set @INNERDONORSQL = @INNERDONORSQL + char(13) + space(6) + 'union' + char(13) + space(6);
set @INNERDONORSQL = isnull(@INNERDONORSQL, '') + 'select ' + @MATCHBACKFIELD + ' from dbo.[' + @DATATABLE + '] where [SEGMENTID] = ''' + cast(@MAILINGSEGMENTID as nvarchar(36)) + '''';
fetch next from MAILINGCURSOR into @DATATABLE, @MAILINGSEGMENTID;
end
close MAILINGCURSOR;
deallocate MAILINGCURSOR;
if @MAILINGSCOUNT > 1
set @INNERDONORSQL = @INNERDONORSQL + char(13) + space(5);
set @SQL = @SQL + 'from (' + @INNERDONORSQL + ') as [DONORS]' + char(13);
if @MARKETINGRECORDTYPE = 2 or @MARKETINGRECORDTYPE = 3
begin
set @PRESQL = 'declare @MAILINGS table([ID] uniqueidentifier not null primary key);' + char(13) +
char(13) +
'insert into @MAILINGS' + char(13) +
' select distinct [MKTSEGMENTATION].[ID]' + char(13) +
' from dbo.[MKTSEGMENTATIONSEGMENT]' + char(13) +
' inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]' + char(13) +
' where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))' + char(13) +
' and [MKTSEGMENTATION].[ACTIVE] = 1' + char(13) +
' and [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID;' + char(13) + char(13);
set @PARAMETERS = @PARAMETERS + ', @SEGMENTID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier, @SECURITYFEATUREID uniqueidentifier, @SECURITYFEATURETYPE tinyint';
if @MARKETINGRECORDTYPE = 2 --List
begin
select
@LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTLIST].[RECORDSOURCEID])
from dbo.[MKTSEGMENTLIST]
inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
where [MKTSEGMENTLIST].[IDSETRECORDTYPEID] = @RECORDTYPEID;
set @SQL = @SQL + 'inner join (select distinct [ID], [GIFTID] from dbo.[' + @LISTMATCHBACKTABLE + '] where [SEGMENTATIONID] in (select [ID] from @MAILINGS)) as [LISTMATCHBACK] on [LISTMATCHBACK].[ID] = [DONORS].[DONORID]' + char(13);
end
else
begin
if @MARKETINGRECORDTYPE = 3 --Consolidated list
begin
declare LISTCURSOR cursor local fast_forward for
select distinct dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTLIST].[RECORDSOURCEID])
from dbo.[MKTSEGMENTLIST]
inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID]
where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID;
open LISTCURSOR;
fetch next from LISTCURSOR into @LISTMATCHBACKTABLE;
while (@@FETCH_STATUS = 0)
begin
if @INNERLISTSQL is not null
set @INNERLISTSQL = @INNERLISTSQL + char(13) + space(12) + 'union' + char(13) + space(12);
set @INNERLISTSQL = isnull(@INNERLISTSQL, '') + 'select distinct [ID], [GIFTID] from dbo.[' + @LISTMATCHBACKTABLE + '] where [SEGMENTATIONID] in (select [ID] from @MAILINGS)';
fetch next from LISTCURSOR into @LISTMATCHBACKTABLE;
end
close LISTCURSOR;
deallocate LISTCURSOR;
if charindex(char(13), @INNERLISTSQL) > 0
set @INNERLISTSQL = @INNERLISTSQL + char(13) + space(11);
set @SQL = @SQL + 'left join (' + @INNERLISTSQL + ') as [LISTMATCHBACK] on cast([LISTMATCHBACK].[ID] as varchar(36)) = [DONORS].[DONORID]' + char(13);
end
end
end
-- loop through all the activated mailings that use the segment and join to each mailing's normal gift ID sets
declare GIFTIDSETCURSOR cursor local fast_forward for
select dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([NORMALGIFTIDSETREGISTERID])
from dbo.[MKTSEGMENTATIONACTIVATE]
where [SEGMENTATIONID] in (select distinct [ID] from @MAILINGSTABLE);
open GIFTIDSETCURSOR;
fetch next from GIFTIDSETCURSOR into @GIFTIDSETNAME;
while (@@FETCH_STATUS = 0)
begin
if @INNERGIFTSQL is not null
set @INNERGIFTSQL = @INNERGIFTSQL + char(13) + space(12) + 'union' + char(13) + space(12);
set @INNERGIFTSQL = isnull(@INNERGIFTSQL, '') + 'select [ID], [DONORID], [FINDERNUMBER], [AMOUNT], [DATE] from dbo.' + @GIFTIDSETNAME;
fetch next from GIFTIDSETCURSOR into @GIFTIDSETNAME;
end
close GIFTIDSETCURSOR;
deallocate GIFTIDSETCURSOR;
if @MAILINGSCOUNT > 1
set @INNERGIFTSQL = @INNERGIFTSQL + char(13) + space(11);
set @SQL = isnull(@PRESQL, '') + @SQL +
'inner join (' + @INNERGIFTSQL + ') as [GIFTIDSETS] on ';
if @MARKETINGRECORDTYPE = 1
set @SQL = @SQL + '[GIFTIDSETS].' + @MATCHBACKFIELD + ' = [DONORS].' + @MATCHBACKFIELD + char(13);
else
begin
if @MARKETINGRECORDTYPE = 2
set @SQL = @SQL + '[GIFTIDSETS].[ID] = [LISTMATCHBACK].[GIFTID]' + char(13);
else
begin
if @MARKETINGRECORDTYPE = 3
set @SQL = @SQL + '[GIFTIDSETS].[ID] = [LISTMATCHBACK].[GIFTID] or cast([GIFTIDSETS].[DONORID] as varchar(36)) = [DONORS].[DONORID]' + char(13);
end
end
set @SQL = @SQL +
'where year([GIFTIDSETS].[DATE]) > year(getdate()) - ' + cast(@NUMYEARS as nvarchar(10)) + char(13) +
'and year([GIFTIDSETS].[DATE]) <= year(getdate())' + char(13) +
'group by year([GIFTIDSETS].[DATE])' + char(13) +
'order by year([GIFTIDSETS].[DATE])';
-- in case no gifts were given, output to a temp table first, then check the rowcount
declare @OUTPUTTABLE table ([YEAR] int, [DONORS] int, [TOTALDONORS] int, [NAME] nvarchar(255));
begin try
-- try to improve performance by adding a join hint to the sql; if the hint fails, then execute the sql without it
declare @SQLWITHJOINHINT nvarchar(max);
set @SQLWITHJOINHINT = @SQL + char(13) + 'option (hash join, merge join);';
if @MARKETINGRECORDTYPE = 2 or @MARKETINGRECORDTYPE = 3
insert into @OUTPUTTABLE
exec sp_executesql @SQLWITHJOINHINT, @PARAMETERS,
@DONORSCOUNT = @DONORSCOUNT,
@SEGMENTNAME = @SEGMENTNAME,
@SEGMENTID = @SEGMENTID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@SECURITYFEATUREID = @SECURITYFEATUREID,
@SECURITYFEATURETYPE = @SECURITYFEATURETYPE;
else
insert into @OUTPUTTABLE
exec sp_executesql @SQLWITHJOINHINT, @PARAMETERS,
@DONORSCOUNT = @DONORSCOUNT,
@SEGMENTNAME = @SEGMENTNAME;
if not (select count(*) from @OUTPUTTABLE) > 0 -- gifts were given
begin -- gifts were not given so enter our basic data so that the report can render something
insert into @OUTPUTTABLE
select year(getdate()) [YEAR], 0 [RETENTION], @DONORSCOUNT [DONORSCOUNT], @SEGMENTNAME [NAME];
end
end try
begin catch
if ERROR_NUMBER() = 8622
begin
if @MARKETINGRECORDTYPE = 2 or @MARKETINGRECORDTYPE = 3
insert into @OUTPUTTABLE
exec sp_executesql @SQL, @PARAMETERS,
@DONORSCOUNT = @DONORSCOUNT,
@SEGMENTNAME = @SEGMENTNAME,
@SEGMENTID = @SEGMENTID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@SECURITYFEATUREID = @SECURITYFEATUREID,
@SECURITYFEATURETYPE = @SECURITYFEATURETYPE;
else
insert into @OUTPUTTABLE
exec sp_executesql @SQL, @PARAMETERS,
@DONORSCOUNT = @DONORSCOUNT,
@SEGMENTNAME = @SEGMENTNAME;
if not (select count(*) from @OUTPUTTABLE) > 0 -- gifts were given
insert into @OUTPUTTABLE
select year(getdate()) [YEAR], 0 [DONORCOUNT], @DONORSCOUNT [TOTALDONORS], @SEGMENTNAME [SEGMENTNAME];
end
else
begin
exec dbo.[USP_RAISE_ERROR];
return 1;
end
end catch
end
else -- the segment is not used yet
begin
insert into @OUTPUTTABLE
select year(getdate()) [YEAR], 0 [DONORCOUNT], @DONORSCOUNT [TOTALDONORS], @SEGMENTNAME [SEGMENTNAME];
end
if not @SEGMENTID is null
begin
declare @YEARTABLE table (
[GIFTYEAR] integer
)
declare @NUMBEROFYEARSTOINSERT integer = @NUMYEARS - 1;
declare @INSERTYEAR bit = 0;
declare @YEAR int;
while @NUMBEROFYEARSTOINSERT >= 0
begin
set @YEAR = year(getdate()) - @NUMBEROFYEARSTOINSERT;
set @INSERTYEAR = case when exists (select 1 from @OUTPUTTABLE AS [RESULTS] where [RESULTS].[YEAR] = @YEAR) then 0 else 1 end;
-- Insert blank year if no gifts given in that year.
if @INSERTYEAR = 1
begin
insert into @YEARTABLE
select @YEAR;
end
set @NUMBEROFYEARSTOINSERT = @NUMBEROFYEARSTOINSERT - 1;
end
insert into @OUTPUTTABLE
select
[GIFTYEAR],
0,
@DONORSCOUNT,
@SEGMENTNAME
from @YEARTABLE;
end
select
[OT].[YEAR],
[OT].[DONORS] AS [RETENTION],
case when ([OT].[TOTALDONORS] - [OT].[DONORS]) is null then 0 else [OT].[TOTALDONORS] - [OT].[DONORS] end as [DONORSCOUNT],
[OT].[NAME]
from @OUTPUTTABLE [OT]
order by [YEAR];
return 0;