USP_MKTSEGMENTATIONLIST_GETRESPONSECOUNTS
Returns the quantity mailed, number of responders, number of responses, total cost, total gift amount, and average gift amount for a list used in marketing efforts.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @LISTID | uniqueidentifier | IN | |
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @SEGMENTID | uniqueidentifier | IN | |
| @PACKAGEID | uniqueidentifier | IN | |
| @ASOFDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONLIST_GETRESPONSECOUNTS]
(
@LISTID uniqueidentifier, --Required. The list to calculate response counts for.
@SEGMENTATIONID uniqueidentifier = null, --Optional. The mailing can be specified with or without the other optional parameters.
@SEGMENTID uniqueidentifier = null, --Optional. The mailing segment can be specified with or without the other optional parameters.
@PACKAGEID uniqueidentifier = null, --Optional. The package (from a mailing segment or test segment) can be specified with or without the other optional parameters.
@ASOFDATE datetime = null --Optional. The "as of" date to calculate response counts up to.
)
as
set nocount on;
declare @RECORDSOURCEID uniqueidentifier;
declare @LISTMATCHBACKTABLE nvarchar(128);
declare @DATATABLE nvarchar(128);
declare @GIFTIDSETNAME nvarchar(255);
declare @SQL nvarchar(max);
declare @LISTIDSQL nvarchar(128);
declare @WHERESQL nvarchar(max);
declare @QUANTITY int;
declare @TOTALCOST money;
declare @RESPONDERS int;
declare @RESPONSES int;
declare @TOTALGIFTAMOUNT money;
declare @TESTSEGMENTID uniqueidentifier;
declare @TESTSAMPLESIZE int;
declare @TESTSAMPLESIZETYPECODE tinyint;
declare @CONSOLIDATEDRECORDTYPES table([RECORDTYPEID] uniqueidentifier not null);
declare @RESPONSECOUNTS table([QUANTITY] int not null, [RESPONDERS] int not null, [RESPONSES] int not null, [TOTALCOST] money not null, [TOTALGIFTAMOUNT] money not null, [ORGANIZATIONTOTALCOST] money not null, [ORGANIZATIONTOTALGIFTAMOUNT] money not null);
declare @ORGANIZATIONTOTALGIFTAMOUNT money;
declare @ORGANIZATIONTOTALCOST money;
begin try
select
@RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID],
@LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTLIST].[RECORDSOURCEID])
from dbo.[MKTLIST]
where [ID] = @LISTID;
--If the list is part of a consolidated list, then get all the record types for the consolidated list...
insert into @CONSOLIDATEDRECORDTYPES ([RECORDTYPEID])
select [IDSETRECORDTYPEID]
from dbo.[MKTSEGMENTLIST]
where [LISTID] = @LISTID
and [CONSOLIDATEDQUERYVIEWID] is not null;
create table #TEMP_LISTRESPONSECOUNTIDS (
[SEGMENTATIONID] uniqueidentifier not null,
[SEGMENTID] uniqueidentifier not null,
[TESTSEGMENTID] uniqueidentifier null,
[PACKAGEID] uniqueidentifier not null,
[TYPECODE] tinyint not null
);
--Get all the information we need for this list...
insert into #TEMP_LISTRESPONSECOUNTIDS ([SEGMENTATIONID], [SEGMENTID], [TESTSEGMENTID], [PACKAGEID], [TYPECODE])
--Imported list segments
select distinct
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATIONSEGMENT].[ID],
null,
[MKTSEGMENTATIONSEGMENT].[PACKAGEID],
[MKTSEGMENTLIST].[TYPECODE]
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[IDSETRECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID] or [MKTSEGMENTLIST].[IDSETRECORDTYPEID] in (select [RECORDTYPEID] from @CONSOLIDATEDRECORDTYPES)
where [MKTSEGMENTLIST].[LISTID] = @LISTID
and [MKTSEGMENTLIST].[TYPECODE] <> 1
and (@SEGMENTATIONID is null or [MKTSEGMENTATION].[ID] = @SEGMENTATIONID)
and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID)
and (@PACKAGEID is null or [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID)
union
--Imported list test segments
select distinct
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATIONSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
[MKTSEGMENTLIST].[TYPECODE]
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[IDSETRECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID] or [MKTSEGMENTLIST].[IDSETRECORDTYPEID] in (select [RECORDTYPEID] from @CONSOLIDATEDRECORDTYPES)
where [MKTSEGMENTLIST].[LISTID] = @LISTID
and [MKTSEGMENTLIST].[TYPECODE] <> 1
and (@SEGMENTATIONID is null or [MKTSEGMENTATION].[ID] = @SEGMENTATIONID)
and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID)
and (@PACKAGEID is null or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @PACKAGEID)
union
--Vendor managed list segments
select distinct
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATIONSEGMENT].[ID],
null,
[MKTSEGMENTATIONSEGMENT].[PACKAGEID],
[MKTSEGMENTLIST].[TYPECODE]
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
where [MKTSEGMENTLIST].[LISTID] = @LISTID
and [MKTSEGMENTLIST].[TYPECODE] = 1
and (@SEGMENTATIONID is null or [MKTSEGMENTATION].[ID] = @SEGMENTATIONID)
and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID)
and (@PACKAGEID is null or [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID)
union
--Vendor managed list test segments
select distinct
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATIONSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
[MKTSEGMENTLIST].[TYPECODE]
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
where [MKTSEGMENTLIST].[LISTID] = @LISTID
and [MKTSEGMENTLIST].[TYPECODE] = 1
and (@SEGMENTATIONID is null or [MKTSEGMENTATION].[ID] = @SEGMENTATIONID)
and (@SEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID)
and (@PACKAGEID is null or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @PACKAGEID);
declare MAILINGCURSOR cursor local fast_forward for
select distinct [SEGMENTATIONID]
from #TEMP_LISTRESPONSECOUNTIDS;
open MAILINGCURSOR;
fetch next from MAILINGCURSOR into @SEGMENTATIONID;
while (@@FETCH_STATUS = 0)
begin
select
@DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([SEGMENTATIONID]),
@GIFTIDSETNAME = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([NORMALGIFTIDSETREGISTERID]),
@QUANTITY = 0,
@TOTALCOST = 0,
@RESPONDERS = 0,
@RESPONSES = 0,
@TOTALGIFTAMOUNT = 0,
@WHERESQL = '',
@ORGANIZATIONTOTALCOST = 0,
@ORGANIZATIONTOTALGIFTAMOUNT = 0
from dbo.[MKTSEGMENTATIONACTIVATE]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [RECORDSOURCEID] = @RECORDSOURCEID;
if @ASOFDATE is not null
set @WHERESQL = 'and [GIFTIDSET].[DATE] <= @ASOFDATE' + char(13);
--Get the imported list quantity and cost (if any exist) from the activated data table...
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TYPECODE] = 0)
begin
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TYPECODE] = 0 and [TESTSEGMENTID] is null)
begin
--Get the quantity for any segments...
set @SQL = 'select' + char(13) +
' @QUANTITY = count([DONORS].[DONORID])' + char(13) +
'from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
'inner join #TEMP_LISTRESPONSECOUNTIDS as [TEMP] on [TEMP].[SEGMENTID] = [DONORS].[SEGMENTID] and [TEMP].[TESTSEGMENTID] is null' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null)' + char(13) +
'inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
'inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID]' + char(13) +
'where [TEMP].[TYPECODE] = 0' + char(13) +
'and [DONORS].[TESTSEGMENTID] is null' + char(13) +
'and [MKTSEGMENTLIST].[LISTID] = @LISTID' + char(13) +
'and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID]';
exec sp_executesql @SQL, N'@LISTID uniqueidentifier, @QUANTITY int output', @LISTID = @LISTID, @QUANTITY = @QUANTITY output;
-- add the cost of any segments
select
@TOTALCOST = @TOTALCOST + isnull(sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]), 0),
@ORGANIZATIONTOTALCOST = @ORGANIZATIONTOTALCOST + isnull(sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]), 0)
from #TEMP_LISTRESPONSECOUNTIDS as [TEMP]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on ([MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [TEMP].[SEGMENTATIONID] and [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID] and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID])
inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null)
where [TEMP].[TYPECODE] = 0;
end
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TYPECODE] = 0 and [TESTSEGMENTID] is not null)
begin
--Get the quantity for any test segments...
set @SQL = 'select' + char(13) +
' @QUANTITY = @QUANTITY + count([DONORS].[DONORID])' + char(13) +
'from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
'inner join #TEMP_LISTRESPONSECOUNTIDS as [TEMP] on [TEMP].[SEGMENTID] = [DONORS].[SEGMENTID] and [TEMP].[TESTSEGMENTID] = [DONORS].[TESTSEGMENTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [TEMP].[TESTSEGMENTID])' + char(13) +
'inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [TEMP].[TESTSEGMENTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]' + char(13) +
'inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID]' + char(13) +
'where [TEMP].[TYPECODE] = 0' + char(13) +
'and [MKTSEGMENTLIST].[LISTID] = @LISTID' + char(13) +
'and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID]';
exec sp_executesql @SQL, N'@LISTID uniqueidentifier, @QUANTITY int output', @LISTID = @LISTID, @QUANTITY = @QUANTITY output;
-- add the cost of any test segments
select
@TOTALCOST = @TOTALCOST + isnull(sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]), 0),
@ORGANIZATIONTOTALCOST = @ORGANIZATIONTOTALCOST + isnull(sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]), 0)
from #TEMP_LISTRESPONSECOUNTIDS as [TEMP]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on ([MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [TEMP].[SEGMENTATIONID] and [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID])
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on ([MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONTESTSEGMENT].[ID] = [TEMP].[TESTSEGMENTID] and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID])
inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID])
where [TEMP].[TYPECODE] = 0;
end
end
--Get the vendor managed list quantity and cost (if any exist) from the individual segments...
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TYPECODE] = 1)
begin
declare SEGMENTCURSOR cursor local fast_forward for
select [SEGMENTID], [TESTSEGMENTID]
from #TEMP_LISTRESPONSECOUNTIDS
where [SEGMENTATIONID] = @SEGMENTATIONID
and [TYPECODE] = 1;
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
while (@@FETCH_STATUS = 0)
begin
--Since this is a vendor managed list segment/test segment, we need to get the total offers from the segment and
--calculate the offers for each test segment taking into account fractions/percents and distributing any remainders.
set @QUANTITY += dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETVENDORMANAGEDQUANTITY](@SEGMENTID, @TESTSEGMENTID);
if @TESTSEGMENTID is null
begin
-- add the total cost for the segment
select
@TOTALCOST = @TOTALCOST + [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST],
@ORGANIZATIONTOTALCOST = @ORGANIZATIONTOTALCOST + [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null)
inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
end
else
begin
-- add the total cost for the test segment
select
@TOTALCOST = @TOTALCOST + [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST],
@ORGANIZATIONTOTALCOST = @ORGANIZATIONTOTALCOST + [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on ([MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID])
inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
end
fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID;
end
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
set @WHERESQL = @WHERESQL + 'and [GIFTIDSET].[SOURCECODE] = [DONORS].[SOURCECODE]';
end
--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';
--Build the sql for retrieving response counts for the list...
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TESTSEGMENTID] is null)
begin
--Get the counts for any segments...
set @SQL = 'select' + char(13) +
' @RESPONDERS = count(distinct([DONORS].[DONORID])),' + char(13) +
' @RESPONSES = count([DONORS].[DONORID]),' + char(13) +
' @TOTALGIFTAMOUNT = isnull(sum([GIFTIDSET].[AMOUNT]),0),' + char(13) +
' @ORGANIZATIONTOTALGIFTAMOUNT = isnull(sum([GIFTIDSET].[ORGANIZATIONAMOUNT]),0)' + char(13) +
'from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
'inner join #TEMP_LISTRESPONSECOUNTIDS as [TEMP] on [TEMP].[SEGMENTID] = [DONORS].[SEGMENTID] and [TEMP].[TESTSEGMENTID] is null' + char(13) +
'inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'inner join dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET] on [GIFTIDSET].[ID] = [LISTDONORS].[GIFTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID]' + char(13) +
'where [DONORS].[TESTSEGMENTID] is null' + char(13) +
'and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID]' + char(13) +
@WHERESQL;
--Execute the SQL to get the response counts...
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @ASOFDATE datetime, @RESPONDERS int output, @RESPONSES int output, @TOTALGIFTAMOUNT money output, @ORGANIZATIONTOTALGIFTAMOUNT money output',
@SEGMENTATIONID = @SEGMENTATIONID,
@ASOFDATE = @ASOFDATE,
@RESPONDERS = @RESPONDERS output,
@RESPONSES = @RESPONSES output,
@TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT output,
@ORGANIZATIONTOTALGIFTAMOUNT = @ORGANIZATIONTOTALGIFTAMOUNT output;
end
if exists(select 1 from #TEMP_LISTRESPONSECOUNTIDS where [SEGMENTATIONID] = @SEGMENTATIONID and [TESTSEGMENTID] is not null)
begin
--Get the counts for any test segments...
set @SQL = 'select' + char(13) +
' @RESPONDERS = @RESPONDERS + count(distinct([DONORS].[DONORID])),' + char(13) +
' @RESPONSES = @RESPONSES + count([DONORS].[DONORID]),' + char(13) +
' @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT + isnull(sum([GIFTIDSET].[AMOUNT]),0),' + char(13) +
' @ORGANIZATIONTOTALGIFTAMOUNT = @ORGANIZATIONTOTALGIFTAMOUNT + isnull(sum([GIFTIDSET].[ORGANIZATIONAMOUNT]),0)' + char(13) +
'from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
'inner join #TEMP_LISTRESPONSECOUNTIDS as [TEMP] on [TEMP].[SEGMENTID] = [DONORS].[SEGMENTID] and [TEMP].[TESTSEGMENTID] = [DONORS].[TESTSEGMENTID]' + char(13) +
'inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'inner join dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET] on [GIFTIDSET].[ID] = [LISTDONORS].[GIFTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [TEMP].[SEGMENTID]' + char(13) +
'inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [TEMP].[TESTSEGMENTID]' + char(13) +
'where [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [TEMP].[PACKAGEID]' + char(13) +
@WHERESQL;
--Execute the SQL to get the response counts...
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @ASOFDATE datetime, @RESPONDERS int output, @RESPONSES int output, @TOTALGIFTAMOUNT money output, @ORGANIZATIONTOTALGIFTAMOUNT money output',
@SEGMENTATIONID = @SEGMENTATIONID,
@ASOFDATE = @ASOFDATE,
@RESPONDERS = @RESPONDERS output,
@RESPONSES = @RESPONSES output,
@TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT output,
@ORGANIZATIONTOTALGIFTAMOUNT = @ORGANIZATIONTOTALGIFTAMOUNT output;
end
insert into @RESPONSECOUNTS (
[QUANTITY],
[RESPONDERS],
[RESPONSES],
[TOTALCOST],
[TOTALGIFTAMOUNT],
[ORGANIZATIONTOTALCOST],
[ORGANIZATIONTOTALGIFTAMOUNT]
) values (
@QUANTITY,
@RESPONDERS,
@RESPONSES,
@TOTALCOST,
@TOTALGIFTAMOUNT,
@ORGANIZATIONTOTALCOST,
@ORGANIZATIONTOTALGIFTAMOUNT
);
fetch next from MAILINGCURSOR into @SEGMENTATIONID;
end
close MAILINGCURSOR;
deallocate MAILINGCURSOR;
--Drop the temp table...
drop table #TEMP_LISTRESPONSECOUNTIDS;
--Return the values
select
isnull(sum([QUANTITY]),0) as [QUANTITY],
isnull(sum([RESPONDERS]),0) as [RESPONDERS],
isnull(sum([RESPONSES]),0) as [RESPONSES],
isnull(sum([TOTALCOST]),0) as [TOTALCOST],
isnull(sum([TOTALGIFTAMOUNT]),0) as [TOTALGIFTAMOUNT],
(case when isnull(sum([RESPONSES]),0) > 0 then cast(sum([TOTALGIFTAMOUNT]) as money) / cast(sum([RESPONSES]) as money) else 0 end) as [AVERAGEGIFTAMOUNT],
isnull(sum([ORGANIZATIONTOTALCOST]),0) as [ORGANIZATIONTOTALCOST],
isnull(sum([ORGANIZATIONTOTALGIFTAMOUNT]),0) as [ORGANIZATIONTOTALGIFTAMOUNT],
(case when isnull(sum([RESPONSES]),0) > 0 then cast(sum([ORGANIZATIONTOTALGIFTAMOUNT]) as money) / cast(sum([RESPONSES]) as money) else 0 end) as [ORGANIZATIONAVERAGEGIFTAMOUNT]
from @RESPONSECOUNTS;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
--Drop the temp table...
drop table #TEMP_LISTRESPONSECOUNTIDS;
return 1;
end catch
return 0;