USP_MKTSMARTFIELD_LOYALTYVALUES
Returns a loyalty score for members of your marketing database.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECORDSOURCEID | uniqueidentifier | IN | |
| @IDSETREGISTERID | uniqueidentifier | IN | |
| @ASOF | datetime | IN | |
| @SITES | xml | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSMARTFIELD_LOYALTYVALUES]
(
@RECORDSOURCEID uniqueidentifier,
@IDSETREGISTERID uniqueidentifier = null,
@ASOF datetime = null,
@SITES xml = null
)
with execute as owner
as
set nocount on;
begin try
-- this runs the analysis in year long buckets
declare @MONTHBIN int = 12;
if not exists(select T.c.value('(SITEID)[1]','uniqueidentifier') from @SITES.nodes('/SITES/ITEM') T(c))
set @SITES = null;
-- if there's an IDset to process, get the info
declare @IDSET sysname;
if @IDSETREGISTERID is not null
select
@IDSET = [DBOBJECTNAME]
from dbo.[IDSETREGISTER]
where [ID] = @IDSETREGISTERID;
-- get the donor view info based on the record source
declare @DONORSYSNAME sysname;
declare @DONORIDFIELD sysname;
declare @DONORDATECHANGEDFIELD sysname;
select
@DONORSYSNAME = [OBJECTNAME],
@DONORIDFIELD = [PRIMARYKEYFIELD],
@DONORDATECHANGEDFIELD = isnull([MKTRECORDSOURCEFIELDMAPPINGS].[DATELASTCHANGEDFIELD],'')
from dbo.[MKTRECORDSOURCE]
left join dbo.[QUERYVIEWCATALOG] on [MKTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
inner join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCE].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
where [MKTRECORDSOURCE].[ID] = @RECORDSOURCEID
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
--if datelastchanged is empty the smart field can not work
if @DONORDATECHANGEDFIELD = ''
raiserror(N'The ''Date last changed'' field must be mapped for this smart field to process.', 15, 4);
-- get the gift view info based on the record source
declare @GIFTSYSNAME sysname;
declare @GIFTDONORIDFIELD sysname;
declare @GIFTDATEFIELD sysname;
declare @GIFTDATECHANGEDFIELD sysname;
declare @GIFTDATEADDEDFIELD sysname;
declare @DATECHANGEDFIELD sysname;
select
@GIFTSYSNAME = [OBJECTNAME],
@GIFTDONORIDFIELD = [DONORIDFIELD],
@GIFTDATEFIELD = [DATEFIELD],
@GIFTDATECHANGEDFIELD = isnull([DATECHANGEDFIELD], ''),
@GIFTDATEADDEDFIELD = isnull([DATEADDEDFIELD], '')
from dbo.[MKTGIFTRECORDSOURCE]
left join dbo.[QUERYVIEWCATALOG] on [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;
--if GIFTDATECHANGEDFIELD and GIFTDATEADDEDFIELD is empty the smart field cannot work
if @GIFTDATECHANGEDFIELD = '' and @GIFTDATEADDEDFIELD = ''
raiserror(N'The gift''s ''Date changed'' field must be mapped for this smart field to process.', 15, 4);
--set the date changed field to what is set
if @GIFTDATECHANGEDFIELD != ''
set @DATECHANGEDFIELD = @GIFTDATECHANGEDFIELD;
else
set @DATECHANGEDFIELD = @GIFTDATEADDEDFIELD;
declare @PROCESSDATE datetime = current_timestamp;
set @PROCESSDATE = cast(year(@PROCESSDATE) as char(4)) + '-' + cast(month(@PROCESSDATE) as char(2)) + '-' + cast(Day(@PROCESSDATE) as char(2));
set @PROCESSDATE = dateadd(d,1,@PROCESSDATE);
set @PROCESSDATE = dateadd(s,-1,@PROCESSDATE);
declare @end0 datetime; set @end0 = @PROCESSDATE;
declare @end1 datetime; set @end1 = dateadd(m,-@MONTHBIN ,@PROCESSDATE);
declare @end2 datetime; set @end2 = dateadd(m,-@MONTHBIN*2,@PROCESSDATE);
declare @end3 datetime; set @end3 = dateadd(m,-@MONTHBIN*3,@PROCESSDATE);
declare @end4 datetime; set @end4 = dateadd(m,-@MONTHBIN*4,@PROCESSDATE);
declare @end5 datetime; set @end5 = dateadd(m,-@MONTHBIN*5,@PROCESSDATE);
declare @end6 datetime; set @end6 = dateadd(m,-@MONTHBIN*6,@PROCESSDATE);
declare @end7 datetime; set @end7 = dateadd(m,-@MONTHBIN*7,@PROCESSDATE);
declare @end8 datetime; set @end8 = dateadd(m,-@MONTHBIN*8,@PROCESSDATE);
declare @end9 datetime; set @end9 = dateadd(m,-@MONTHBIN*9,@PROCESSDATE);
declare @end10 datetime; set @end10 = dateadd(m,-@MONTHBIN*10,@PROCESSDATE);
declare @start0 datetime; set @start0 = dateadd(s,+1,@end1);
declare @start1 datetime; set @start1 = dateadd(s,+1,@end2);
declare @start2 datetime; set @start2 = dateadd(s,+1,@end3);
declare @start3 datetime; set @start3 = dateadd(s,+1,@end4);
declare @start4 datetime; set @start4 = dateadd(s,+1,@end5);
declare @start5 datetime; set @start5 = dateadd(s,+1,@end6);
declare @start6 datetime; set @start6 = dateadd(s,+1,@end7);
declare @start7 datetime; set @start7 = dateadd(s,+1,@end8);
declare @start8 datetime; set @start8 = dateadd(s,+1,@end9);
declare @start9 datetime; set @start9 = dateadd(s,+1,@end10);
declare @startOld datetime; set @startOld = '1900-01-01';
declare @PARMDEFINITION nvarchar(1024);
set @PARMDEFINITION = N'@start0 datetime, @start1 datetime, @start2 datetime, @start3 datetime, @start4 datetime, @start5 datetime, @start6 datetime, @start7 datetime, @start8 datetime, @start9 datetime, @startOld datetime,' +
N'@end0 datetime, @end1 datetime, @end2 datetime, @end3 datetime, @end4 datetime, @end5 datetime, @end6 datetime, @end7 datetime, @end8 datetime, @end9 datetime, @end10 datetime';
declare @ISVALID_BBECRECORDSOURCE as bit = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID);
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);
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
[FINANCIALTRANSACTION].[ID]
from dbo.[FINANCIALTRANSACTION]
inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
cross apply dbo.[UFN_SITEID_MAPFROM_REVENUEID]([FINANCIALTRANSACTION].[ID]) as [SITES]
inner join @SITESFILTER as [SITESFILTER] on [SITESFILTER].[ID] = [SITES].[SITEID]
where [FINANCIALTRANSACTION].[DELETEDON] is null;
end
declare @SQL nvarchar(max);
if @ASOF is null
begin
set @SQL = 'with [VALUES] as ' + char(13) +
'( ' + char(13) +
' select ' + char(13) +
' [C].['+@DONORIDFIELD+'], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start0 and @end0 then 1 else 0 end)) as [YEAR1], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start1 and @end1 then 1 else 0 end)) as [YEAR2], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start2 and @end2 then 1 else 0 end)) as [YEAR3], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start3 and @end3 then 1 else 0 end)) as [YEAR4], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start4 and @end4 then 1 else 0 end)) as [YEAR5], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start5 and @end5 then 1 else 0 end)) as [YEAR6], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start6 and @end6 then 1 else 0 end)) as [YEAR7], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start7 and @end7 then 1 else 0 end)) as [YEAR8], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start8 and @end8 then 1 else 0 end)) as [YEAR9], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start9 and @end9 then 1 else 0 end)) as [YEAR10], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @startOld and @end10 then 1 else 0 end)) as [OLDER] ' + char(13) +
' from dbo.['+@DONORSYSNAME+'] as [C] ' + char(13) +
' left outer join ['+@GIFTSYSNAME+'] as [G] on [C].['+@DONORIDFIELD+'] = [G].['+@GIFTDONORIDFIELD+'] ' + char(13);
if @IDSET is not null
set @SQL += ' inner join dbo.[' + @IDSET + '] on ['+@DONORSYSNAME+'].['+@DONORIDFIELD+'] = [' + @IDSET + '].[ID] ' + char(13);
if @ISVALID_BBECRECORDSOURCE = 1 and @SITES is not null
set @SQL += ' inner join #TMP_REVENUESITEFILTER as REVENUESITEFILTER on REVENUESITEFILTER.ID = [G].[ID] ' + CHAR(13);
if @SITES is not null
set @SQL += 'where (@SITES is null) or exists(select 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [C].[' + @DONORIDFIELD + '])' + char(13);
set @SQL += ' group by [C].[' + @DONORIDFIELD + '] ' + char(13) +
') ' + char(13) +
'select ' + char(13) +
' [VALUES].[' + @DONORIDFIELD + '], ' + char(13) +
' dbo.[UFN_MKTSMARTFIELD_LOYALTY_2]([VALUES].[YEAR1], [VALUES].[YEAR2], [VALUES].[YEAR3], [VALUES].[YEAR4], [VALUES].[YEAR5], [VALUES].[YEAR6], [VALUES].[YEAR7], [VALUES].[YEAR8], [VALUES].[YEAR9], [VALUES].[YEAR10], [VALUES].[OLDER]) ' + char(13) +
'from [VALUES];';
if @SITES is null
exec sp_executesql @SQL, @PARMDEFINITION, @start0 = @start0, @start1 = @start1, @start2 = @start2, @start3 = @start3, @start4 = @start4, @start5 = @start5, @start6 = @start6, @start7 = @start7, @start8 = @start8, @start9 = @start9, @startOld = @startOld, @end0 = @end0, @end1 = @end1, @end2 = @end2, @end3 = @end3, @end4 = @end4, @end5 = @end5, @end6 = @end6, @end7 = @end7, @end8 = @end8, @end9 = @end9, @end10 = @end10;
else
begin
set @PARMDEFINITION += N', @SITES xml';
exec sp_executesql @SQL, @PARMDEFINITION, @start0 = @start0, @start1 = @start1, @start2 = @start2, @start3 = @start3, @start4 = @start4, @start5 = @start5, @start6 = @start6, @start7 = @start7, @start8 = @start8, @start9 = @start9, @startOld = @startOld, @end0 = @end0, @end1 = @end1, @end2 = @end2, @end3 = @end3, @end4 = @end4, @end5 = @end5, @end6 = @end6, @end7 = @end7, @end8 = @end8, @end9 = @end9, @end10 = @end10, @SITES = @SITES;
end
end
else
begin
set @PARMDEFINITION += N', @ASOF datetime';
--Find all affected constituents
set @SQL = 'with CONSTITS ([ID]) as' + char(13) +
'(' + char(13) +
' select [VIEW].[' + @GIFTDONORIDFIELD + ']' + char(13) +
' from dbo.[' + @GIFTSYSNAME + '] [VIEW]' + char(13);
if @SITES is not null
set @SQL += 'where (@SITES is null) or exists(select 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [VIEW].[' + @GIFTDONORIDFIELD + '])' + char(13);
set @SQL += ' group by [VIEW].[' + @GIFTDONORIDFIELD + ']' + char(13) +
' having max([VIEW].[' + @DATECHANGEDFIELD + ']) > @ASOF' + char(13);
if @ISVALID_BBECRECORDSOURCE = 1
set @SQL += dbo.[UFN_MKTSMARTFIELD_GETEXTRACONSTITS_DYNAMICSQL_2](@SITES);
set @SQL += ')' + char(13) +
', [CALC] as' + char(13) +
'(' + char(13) +
' select ' + char(13) +
' [C].[' + @DONORIDFIELD + '], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start0 and @end0 then 1 else 0 end)) as [YEAR1], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start1 and @end1 then 1 else 0 end)) as [YEAR2], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start2 and @end2 then 1 else 0 end)) as [YEAR3], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start3 and @end3 then 1 else 0 end)) as [YEAR4], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start4 and @end4 then 1 else 0 end)) as [YEAR5], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start5 and @end5 then 1 else 0 end)) as [YEAR6], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start6 and @end6 then 1 else 0 end)) as [YEAR7], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start7 and @end7 then 1 else 0 end)) as [YEAR8], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start8 and @end8 then 1 else 0 end)) as [YEAR9], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @start9 and @end9 then 1 else 0 end)) as [YEAR10], ' + char(13) +
' sum((case when [G].[' + @GIFTDATEFIELD + '] between @startOld and @end10 then 1 else 0 end)) as [OLDER] ' + char(13) +
' from dbo.[' + @DONORSYSNAME + '] as [C] ' + char(13) +
' left outer join [' + @GIFTSYSNAME + '] as [G] on [C].[' + @DONORIDFIELD + '] = [G].[' + @GIFTDONORIDFIELD + '] ' + char(13);
if @IDSET is not null
set @SQL += ' inner join dbo.[' + @IDSET + '] on [' + @DONORSYSNAME + '].[' + @DONORIDFIELD + ']=[' + @IDSET + '].[ID] ' + char(13);
if @ISVALID_BBECRECORDSOURCE = 1 and @SITES is not null
set @SQL += ' inner join #TMP_REVENUESITEFILTER as REVENUESITEFILTER on REVENUESITEFILTER.ID = [G].[ID] ' + char(13);
if @SITES is not null
set @SQL += 'where (@SITES is null) or exists(select 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [C].[' + @DONORIDFIELD + '])' + char(13);
set @SQL += ' group by [C].[' + @DONORIDFIELD + '] ' + char(13) +
') ' + char(13) +
--Return affected constituents with new values
'select [CONSTITS].[ID], dbo.[UFN_MKTSMARTFIELD_LOYALTY_2]([CALC].[YEAR1], [CALC].[YEAR2], [CALC].[YEAR3], [CALC].[YEAR4], [CALC].[YEAR5], [CALC].[YEAR6], [CALC].[YEAR7], [CALC].[YEAR8], [CALC].[YEAR9], [CALC].[YEAR10], [CALC].[OLDER]) ' + char(13) +
'from [CONSTITS]' + char(13) +
'left join [CALC] on [CALC].[' + @DONORIDFIELD + '] = [CONSTITS].[ID];' + char(13);
if @SITES is null
exec sp_executesql @SQL, @PARMDEFINITION, @start0 = @start0, @start1 = @start1, @start2 = @start2, @start3 = @start3, @start4 = @start4, @start5 = @start5, @start6 = @start6, @start7 = @start7, @start8 = @start8, @start9 = @start9, @startOld=@startOld, @end0 = @end0, @end1 = @end1, @end2 = @end2, @end3 = @end3, @end4 = @end4, @end5 = @end5, @end6 = @end6, @end7 = @end7, @end8 = @end8, @end9 = @end9, @end10 = @end10, @ASOF = @ASOF;
else
begin
set @PARMDEFINITION += ', @SITES xml';
exec sp_executesql @SQL, @PARMDEFINITION, @start0 = @start0, @start1 = @start1, @start2 = @start2, @start3 = @start3, @start4 = @start4, @start5 = @start5, @start6 = @start6, @start7 = @start7, @start8 = @start8, @start9 = @start9, @startOld=@startOld, @end0 = @end0, @end1 = @end1, @end2 = @end2, @end3 = @end3, @end4 = @end4, @end5 = @end5, @end6 = @end6, @end7 = @end7, @end8 = @end8, @end9 = @end9, @end10 = @end10, @ASOF = @ASOF, @SITES = @SITES;
end
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;