USP_CONSTITUENTSMARTFIELD_AGGREGATERECOGNITIONAMOUNTS
Returns aggregate recognition credit values to be used in various constituent revenue smart fields.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @TYPECODES | xml | IN | |
| @DESIGNATIONS | xml | IN | |
| @CALCULATIONTYPE | tinyint | IN | |
| @ASOF | datetime | IN | |
| @CAMPAIGNS | xml | IN | |
| @CURRENCYID | uniqueidentifier | IN | |
| @SMARTFIELDID | uniqueidentifier | IN | |
| @SITES | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_AGGREGATERECOGNITIONAMOUNTS
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@TYPECODES xml,
@DESIGNATIONS xml,
@CALCULATIONTYPE tinyint = 0,
@ASOF datetime,
@CAMPAIGNS xml = null,
@CURRENCYID uniqueidentifier = null,
@SMARTFIELDID uniqueidentifier = null,
@SITES xml = null
)
with execute as owner
as
/*
This smart field will return an aggregation of recognition values for ALL constituents in the database
*/
set nocount on;
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
set @CURRENCYID = isnull(@CURRENCYID, @ORGANIZATIONCURRENCYID);
select
@DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
@ROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
from
dbo.[CURRENCY]
where
[CURRENCY].[ID] = @CURRENCYID;
declare @SITESFILTER table(ID uniqueidentifier primary key);
insert into
@SITESFILTER(ID)
select distinct
T.c.value('(SITEID)[1]','uniqueidentifier')
FROM
@SITES.nodes('/SITES/ITEM') T(c)
create table #CONSTITUENTSITEFITLER (ID uniqueidentifier primary key);
insert into
#CONSTITUENTSITEFITLER(ID)
select distinct
CONSTITUENTSITE.CONSTITUENTID
from
CONSTITUENTSITE
inner join
@SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID
if object_id('tempdb..#TMP_REVENUESITEFILTER') is not null
drop table #TMP_REVENUESITEFILTER
create table #TMP_REVENUESITEFILTER(ID uniqueidentifier);
if @SITES is not null
begin
insert into #TMP_REVENUESITEFILTER
select distinct
REVENUE.ID
from
dbo.FINANCIALTRANSACTION as REVENUE
cross apply
dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITES
inner join
@SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
where REVENUE.DELETEDON is null
end
/*
Find all valid constituents based on the date that the smart field was last updated. If there is no @ASOF value then all constituents need to be processed.
*/
declare @CONSTITUENTFILTERVIEW as nvarchar(50) = '#TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS'
if object_id('tempdb..#TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS') is not null
drop table #TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS
create table #TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS
(
ID uniqueidentifier primary key
)
if @ASOF is null
begin
if @SITES is null
begin
set @CONSTITUENTFILTERVIEW = 'dbo.CONSTITUENT'
end
else
begin
insert into #TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS(ID)
select ID from #CONSTITUENTSITEFITLER
end
end
else
begin
if @SITES is null
begin
insert into #TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS(ID)
select CONSTITUENTID from dbo.REVENUERECOGNITION where REVENUERECOGNITION.DATECHANGED > @ASOF and CONSTITUENTID is not null
union select CONSTITUENTID from dbo.REVENUERECOGNITIONAUDIT inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITIONAUDIT.CONSTITUENTID where REVENUERECOGNITIONAUDIT.AUDITDATE > @ASOF and CONSTITUENTID is not null
union select CONSTITUENTID from dbo.RECOGNITIONCREDIT where RECOGNITIONCREDIT.DATECHANGED > @ASOF and CONSTITUENTID is not null
union select CONSTITUENTID from dbo.RECOGNITIONCREDITAUDIT inner join dbo.CONSTITUENT on CONSTITUENT.ID = RECOGNITIONCREDITAUDIT.CONSTITUENTID where RECOGNITIONCREDITAUDIT.AUDITDATE > @ASOF
union select CONSTITUENTID from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null
union select CONSTITUENTID from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID where REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUEAUDIT.AUDITDATE > @ASOF
union select CONSTITUENTID from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTION.DELETEDON is null
union select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;
end
else
begin
insert into #TMP_CONSTITUENTS_AGGREGATERECOGNITIONAMOUNTS(ID)
select CONSTITUENTID from dbo.REVENUERECOGNITION
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTID
where REVENUERECOGNITION.DATECHANGED > @ASOF and CONSTITUENTID is not null
union
select CONSTITUENTID from dbo.REVENUERECOGNITIONAUDIT
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITIONAUDIT.CONSTITUENTID
where REVENUERECOGNITIONAUDIT.AUDITDATE > @ASOF
union
select CONSTITUENTID from dbo.FINANCIALTRANSACTION
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTID
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null
union
select REVENUEAUDIT.CONSTITUENTID from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select CONSTITUENTID from dbo.FINANCIALTRANSACTION
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select CONSTITUENT.ID from dbo.CONSTITUENT
inner join #CONSTITUENTSITEFITLER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENT.ID
where DATEADDED > @ASOF;
end
end
drop table #CONSTITUENTSITEFITLER;
declare @sql as nvarchar(max);
set @sql = '
declare @GIFTTYPESFILTER table(TYPECODE tinyint, APPLICATIONCODE tinyint primary key (TYPECODE, APPLICATIONCODE));
declare @DESIGNATIONSFILTER table(ID uniqueidentifier primary key);
declare @CAMPAIGNSFILTER table(ID uniqueidentifier primary key);
/*@TYPECODES will not be null if this SP is driven from the UI. Just in case, apply the default value.*/
if @TYPECODES is null
begin
insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE) values (0,0)
end
else
begin
insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE)
select distinct
case TYPECODEID
when 1 then 1 /*Pledge*/
when 2 then 2 /*Recurring gift*/
when 9 then 4 /*Planned gift*/
when 14 then 8 /*Donor challenge claim*/
else 0
end as TYPECODE,
case TYPECODEID
when 4 then 2 /*Pledge payment*/
when 5 then 3 /*RG payment*/
when 6 then 1 /*Event registration fee*/
when 8 then 7 /*Matching gift payment*/
when 10 then 6 /*Planned gift payment*/
when 11 then 5 /*Membership*/
when 12 then 4 /*Other*/
when 13 then 9 /*Admission*/
when 15 then 13 /*Donor challenge payment*/
else 0 /*Gift, pledge, and recurring gift*/
end as APPLICATIONCODE
from (
select
T.c.value(''(TYPECODEID)[1]'',''tinyint'') as TYPECODEID
from
@TYPECODES.nodes(''/TYPECODES/ITEM'') T(c)
) TYPECODES
if exists (select 1 from @GIFTTYPESFILTER where TYPECODE = 1)
insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE)
select 1,5 union all select 1,4 /*Add pledges applied to memberships and applied to other*/
if exists (select 1 from @GIFTTYPESFILTER where TYPECODE = 2)
insert into @GIFTTYPESFILTER(TYPECODE,APPLICATIONCODE)
select 2,5 union all select 2,4 /*Add recurring gifts applied to memberships and applied to other*/
/* Allow for donations made from sales orders */
if exists (select 1 from @GIFTTYPESFILTER where TYPECODE = 0)
insert into @GIFTTYPESFILTER(TYPECODE, APPLICATIONCODE)
select 5,0
end
if @DESIGNATIONS is not null
begin
insert into @DESIGNATIONSFILTER(ID) select distinct
T.c.value(''(ID)[1]'',''uniqueidentifier'')
FROM @DESIGNATIONS.nodes(''/DESIGNATIONS/ITEM'') T(c);
end;
if @CAMPAIGNS is not null
begin
insert into @CAMPAIGNSFILTER(ID) select distinct
T.c.value(''(ID)[1]'',''uniqueidentifier'')
FROM @CAMPAIGNS.nodes(''/CAMPAIGNS/ITEM'') T(c);
end;'
/*
Retrieve recognition data based on filter criteria. Only revenue data affecting constituents modified since the @ASOF value will be returned by this SP.
*/
set @sql = @sql + char(10) + 'with REVENUE_CTE (ID, CONSTITUENTID, AMOUNT, DATE, ROW) as
(select
[RECOGNITIONAMOUNTINCURRENCY].[ID],
[RECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID],
[RECOGNITIONAMOUNTINCURRENCY].[AMOUNTINCURRENCY] - isnull([REFUNDSPLIT].[REFUNDTOTAL],0),
[RECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE],'
-- Include a row number if needed for calculating an extreme.
if @CALCULATIONTYPE = 6
set @sql = @sql + char(10) + 'ROW_NUMBER() over (partition by [RECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] order by [RECOGNITIONAMOUNTINCURRENCY].[AMOUNTINCURRENCY] desc, [RECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE] desc) as ROW'
else if @CALCULATIONTYPE = 11
set @sql = @sql + char(10) + 'ROW_NUMBER() over (partition by [RECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] order by [RECOGNITIONAMOUNTINCURRENCY].[DATE] asc, [RECOGNITIONAMOUNTINCURRENCY].[REVENUEDATEADDED] asc) as ROW'
else if @CALCULATIONTYPE = 12
set @sql = @sql + char(10) + 'ROW_NUMBER() over (partition by [RECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] order by [RECOGNITIONAMOUNTINCURRENCY].[DATE] desc, [RECOGNITIONAMOUNTINCURRENCY].[REVENUEDATEADDED] desc) as ROW'
else
set @sql = @sql + char(10) + '0 as ROW'
set @sql = @sql + char(10) + '
from
(
select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID, RR.REVENUEDATEADDED, RR.DESIGNATIONID
from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
union all
select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID, RC.REVENUEDATEADDED, RC.DESIGNATIONID
from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RC
) RECOGNITIONAMOUNTINCURRENCY
inner join @GIFTTYPESFILTER as GIFTTYPESFILTER on [GIFTTYPESFILTER].[TYPECODE] = [RECOGNITIONAMOUNTINCURRENCY].[TRANSACTIONTYPECODE] and [GIFTTYPESFILTER].[APPLICATIONCODE] = [RECOGNITIONAMOUNTINCURRENCY].[APPLICATIONCODE]
--Accounting for refunds
left join dbo.UFN_CREDIT_GETSPLITREFUNDAMOUNT_BULK() REFUNDSPLIT on REFUNDSPLIT.SOURCELINEITEMID = [RECOGNITIONAMOUNTINCURRENCY].REVENUESPLITID'
if @SITES is not null
set @sql = @sql + char(10) + ' inner join #TMP_REVENUESITEFILTER as REVENUESITEFILTER on REVENUESITEFILTER.ID = RECOGNITIONAMOUNTINCURRENCY.REVENUEID'
-- If the constituent filter is all constituents, then simply filtering out null is faster than joining
if @ASOF is not null
set @sql = @sql + char(10) + 'inner join ' + @CONSTITUENTFILTERVIEW + ' as [CONSTITUENTFILTER] on [CONSTITUENTFILTER].[ID] = [RECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID]'
set @sql = @sql + char(10) + 'where'
if @ASOF is null
set @sql = @sql + char(10) + '[RECOGNITIONAMOUNTINCURRENCY].[REVENUECONSTITUENTID] is not null and'
set @sql = @sql + char(10) + '
((@STARTDATE is null) or ([RECOGNITIONAMOUNTINCURRENCY].[DATE] >= @STARTDATE))
and ((@ENDDATE is null) or ([RECOGNITIONAMOUNTINCURRENCY].[DATE] <= @ENDDATE))'
if @DESIGNATIONS is not null
set @sql = @sql + char(10) + '
and (
(
[RECOGNITIONAMOUNTINCURRENCY].[DESIGNATIONID] in (
select ID
from @DESIGNATIONSFILTER as DESIGNATIONSFILTER
)
)
or
(
[RECOGNITIONAMOUNTINCURRENCY].TRANSACTIONTYPECODE = 0 and [RECOGNITIONAMOUNTINCURRENCY].APPLICATIONCODE = 1
)
)'
if @CAMPAIGNS is not null
set @sql = @sql + char(10) + '
and (
(
[RECOGNITIONAMOUNTINCURRENCY].REVENUESPLITID in (select REVENUESPLITCAMPAIGN.REVENUESPLITID from dbo.REVENUESPLITCAMPAIGN inner join @CAMPAIGNSFILTER as CAMPAIGNSFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNSFILTER.ID)
)
or
(
[RECOGNITIONAMOUNTINCURRENCY].TRANSACTIONTYPECODE = 0 and [RECOGNITIONAMOUNTINCURRENCY].APPLICATIONCODE = 1
)
)';
set @sql = @sql + ')';
-- Backwards compatibility
if @SMARTFIELDID is not null
set @sql = @sql + ', SF_CTE (ID, VALUE) as ('
/*
Calculate totals based on calculation type.
*/
if @CALCULATIONTYPE = 0 --Max(amount)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(max(REVENUERECORDS.AMOUNT),0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 1 --Min(amount)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(min(REVENUERECORDS.AMOUNT),0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 2 --Sum(amount)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(sum(REVENUERECORDS.AMOUNT),0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 3 --Avg(amount)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(avg(REVENUERECORDS.AMOUNT),0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 4 --Min(date)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
min(REVENUERECORDS.DATE) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 5 --Max(date)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
max(REVENUERECORDS.DATE) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 6 --Largest revenue date
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
REVENUERECORDS.DATE as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
REVENUERECORDS.ID is null or REVENUERECORDS.ROW = 1';
else if @CALCULATIONTYPE = 7 --Giving years
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), 0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 8 --Distinct giving years
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
count(REVENUEDATES.YEAR) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (
select distinct
REVENUERECORDS.CONSTITUENTID,
datepart(year, REVENUERECORDS.DATE) as YEAR
from REVENUE_CTE as REVENUERECORDS
) as REVENUEDATES on REVENUEDATES.CONSTITUENTID = CONSTITUENTFILTER.ID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 9 --Is annual giver
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
case
when
coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), 0) = (count(distinct datepart(year, REVENUERECORDS.DATE)) - 1)
and count(distinct datepart(year, REVENUERECORDS.DATE)) > 1
then
cast(1 as bit)
else
cast(0 as bit)
end as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE = 10 --Count(revenue)
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
count(REVENUERECORDS.ID) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by
CONSTITUENTFILTER.ID';
else if @CALCULATIONTYPE in (11, 12) --First revenue amount, Latest revenue amount
set @sql = @sql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(REVENUERECORDS.AMOUNT, 0) as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
REVENUERECORDS.ID is null or REVENUERECORDS.ROW = 1';
-- Backwards compatibility
if @SMARTFIELDID is null
exec sp_executesql @sql, N'@ASOF datetime,
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint,
@STARTDATE datetime,
@ENDDATE datetime,
@TYPECODES xml,
@DESIGNATIONS xml,
@CAMPAIGNS xml
',
@ASOF,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
@STARTDATE,
@ENDDATE,
@TYPECODES,
@DESIGNATIONS,
@CAMPAIGNS
else
begin
declare @TARGET nvarchar(100)
declare @TARGET_FIELD nvarchar(128)
select
@TARGET = TABLECATALOG.TABLENAME,
@TARGET_FIELD = SMARTFIELD.VALUECOLUMNNAME
from dbo.SMARTFIELD
left join dbo.TABLECATALOG on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
where SMARTFIELD.ID = @SMARTFIELDID
set @sql = @sql + ')
merge dbo.' + @TARGET + ' as target
using SF_CTE as source
on target.ID = source.ID
when matched then
update set ' + @TARGET_FIELD + ' = source.VALUE
when not matched by target then
insert (ID, ' + @TARGET_FIELD + ')
values (source.ID, source.VALUE);';
exec sp_executesql @sql, N'@ASOF datetime,
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint,
@STARTDATE datetime,
@ENDDATE datetime,
@TYPECODES xml,
@DESIGNATIONS xml,
@CAMPAIGNS xml
',
@ASOF,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
@STARTDATE,
@ENDDATE,
@TYPECODES,
@DESIGNATIONS,
@CAMPAIGNS;
select @@ROWCOUNT;
end