UFN_COMMUNICATIONS_GETCONSTITUENTTOTALCOMMUNICATIONS

This function returns the total number of communications per constituent within the given range of the given date.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@PERIODTYPECODE	tinyint	IN
@NUMPERIODS	int	IN
@STARTDATE	datetime	IN
@COMMUNICATIONTYPES	xml	IN
Definition

Copy


CREATE function dbo.UFN_COMMUNICATIONS_GETCONSTITUENTTOTALCOMMUNICATIONS
(
    @PERIODTYPECODE tinyint,
    @NUMPERIODS integer,
    @STARTDATE datetime,
    @COMMUNICATIONTYPES xml
)
returns @RESULTS table ([CONSTITUENTID] uniqueidentifier, [TOTALCOMMUNICATIONS] integer)
as
begin

    -- @PERIODTYPECODE

    --        0 - week

    --        1 - month

    --        2 - quarter

    --        3 - year


    -- @COMMUNICATIONTYPES collection of communication types to be considered

    --        <COMMUNICATIONTYPES><ITEM><COMMUNICATIONTYPECODE>?</COMMUNICATIONTYPECODE></ITEM></COMMUNICATIONTYPES>

    --    0 - Appeal communications

    --    1 - Event invitations

    --    2 - Membership renewal

    --    3 - Pledge reminder

    --    4 - Sponsorship effort

    --    5 - Marketing effort


    declare @INCLUDEAPPEALCOMMUNICATIONS bit = 0;
    declare @INCLUDEEVENTINVITATIONS bit = 0;
    declare @INCLUDEMEMBERSHIPRENEWAL bit = 0;
    declare @INCLUDEPLEDGEREMINDER bit = 0;
    declare @INCLUDESPONSORSHIPEFFORT bit = 0;
    declare @INCLUDEMARKETINGEFFORT bit = 0;

    -- Parse communication types

    declare @COMMUNICATIONTYPECODE tinyint;

    declare TYPESCURSOR cursor local fast_forward for
        select
            COMMUNICATIONTYPECODE
        from dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES_FROMITEMLISTXML(@COMMUNICATIONTYPES);

    open TYPESCURSOR;
    fetch next from TYPESCURSOR into @COMMUNICATIONTYPECODE;

    while (@@FETCH_STATUS = 0)
    begin
        if @COMMUNICATIONTYPECODE = 0
            set @INCLUDEAPPEALCOMMUNICATIONS = 1;

        else if @COMMUNICATIONTYPECODE = 1
            set @INCLUDEEVENTINVITATIONS = 1;

        else if @COMMUNICATIONTYPECODE = 2
            set @INCLUDEMEMBERSHIPRENEWAL = 1;

        else if @COMMUNICATIONTYPECODE = 3
            set @INCLUDEPLEDGEREMINDER = 1;

        else if @COMMUNICATIONTYPECODE = 4
            set @INCLUDESPONSORSHIPEFFORT = 1;

        else if @COMMUNICATIONTYPECODE = 5
            set @INCLUDEMARKETINGEFFORT = 1;

        fetch next from TYPESCURSOR into @COMMUNICATIONTYPECODE;
    end

    close TYPESCURSOR;
    deallocate TYPESCURSOR;

    insert into @RESULTS    
        select
            TOTALS.CONSTITUENTID,
            sum(TOTALS.NUMCOMMUNICATIONS) TOTALCOMMUNICATIONS
        from dbo.UFN_COMMUNICATIONS_GETCONSTITUENTTOTALCOMMUNICATIONSPERTYPE(@PERIODTYPECODE, @NUMPERIODS, @STARTDATE, 
                            @INCLUDEAPPEALCOMMUNICATIONS, @INCLUDEAPPEALCOMMUNICATIONS, @INCLUDEMEMBERSHIPRENEWAL, 
                            @INCLUDEPLEDGEREMINDER, @INCLUDESPONSORSHIPEFFORT, @INCLUDEMARKETINGEFFORT) TOTALS
        group by TOTALS.CONSTITUENTID;

    return;

end