UFN_COMMUNICATIONS_GETCONSTITUENTTOTALREVENUE

This function returns total revenue 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

Definition

Copy



CREATE function dbo.UFN_COMMUNICATIONS_GETCONSTITUENTTOTALREVENUE
(
    @PERIODTYPECODE tinyint,
    @NUMPERIODS integer,
    @STARTDATE as datetime
)
returns @RESULTS table ([CONSTITUENTID] uniqueidentifier, [TOTALREVENUEAMOUNT] money)
as
begin
    -- @PERIODTYPECODE

    --        0 - week

    --        1 - month

    --        2 - quarter

    --        3 - year


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


    insert into @RESULTS
        select 
            REVENUE.CONSTITUENTID,
            SUM(REVENUESPLIT.AMOUNT) as TOTALREVENUEAMOUNT
        from dbo.REVENUE
        inner join dbo.REVENUESPLIT
            on REVENUE.ID = REVENUESPLIT.REVENUEID
        inner join dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() as VALIDREVENUE
            on REVENUESPLIT.ID = VALIDREVENUE.REVENUESPLITID
        where
            (
                (@PERIODTYPECODE = 0 and REVENUE.[DATE] between DATEADD(ww, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                or (@PERIODTYPECODE = 1 and REVENUE.[DATE] between DATEADD(mm, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                or (@PERIODTYPECODE = 2 and REVENUE.[DATE] between DATEADD(qq, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                or (@PERIODTYPECODE = 3 and REVENUE.[DATE] between DATEADD(yy, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
            )                         
        group by REVENUE.CONSTITUENTID;

    return;
end