UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTREVENUE

This function returns the constituents with any of given revenue types within the given time period from the given start date.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@PERIODTYPECODE	tinyint	IN
@NUMPERIODS	int	IN
@STARTDATE	datetime	IN

Definition

Copy



CREATE function dbo.UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTREVENUE
(
    @PERIODTYPECODE tinyint,
    @NUMPERIODS integer,
    @STARTDATE as datetime
)
returns @CONSTITUENTS table ([CONSTITUENTID] uniqueidentifier)
as
begin
    declare @BEGINDATE date =
        case @PERIODTYPECODE
            when 0 then  -- week

                dateadd(week, -@NUMPERIODS, @STARTDATE)
            when 1 then  -- month

                dateadd(month, -@NUMPERIODS, @STARTDATE)
            when 2 then  -- quarter

                dateadd(quarter, -@NUMPERIODS, @STARTDATE)
            when 3 then  -- year

                dateadd(year, -@NUMPERIODS, @STARTDATE)
        end;

    -- Valid Revenue Types are now taken from the Donor Lifecycle functionality


    insert into @CONSTITUENTS
        select distinct
            FINANCIALTRANSACTION.CONSTITUENTID
        from
            dbo.FINANCIALTRANSACTION
        inner join
            dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join
            dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() as VALIDREVENUE on VALIDREVENUE.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        where
            FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1  -- Reversal

            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTION.CONSTITUENTID is not null
            and FINANCIALTRANSACTION.CALCULATEDDATE between @BEGINDATE and @STARTDATE;

    return;
end