UFN_DESIGNATIONLEVEL_GETREVENUETOTALSINCURRENCY_2

Returns revenue totals for gifts, donors, and amounts (revenue as opposed to raised)

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@DESIGNATIONLEVELID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
@DECIMALDIGITS	tinyint	IN
@ROUNDINGTYPECODE	tinyint	IN

Definition

Copy



CREATE function dbo.UFN_DESIGNATIONLEVEL_GETREVENUETOTALSINCURRENCY_2
(
    @DESIGNATIONLEVELID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @CURRENCYID uniqueidentifier = null,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint
)
returns table
as
return(
    with DESIGNATIONS_CTE as (
        select
            D.ID
        from dbo.DESIGNATION D
        where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
        )
        select
            coalesce(sum(DATA.TOTALREVENUE), 0) as TOTALREVENUE,
            count(distinct DATA.CONSTITUENTID) as NUMDONORS,
            count(distinct DATA.REVENUEID) as NUMGIFTS
        from
            (
                select  RS.AMOUNTINCURRENCY - (coalesce(WO.AMOUNTINCURRENCY, 0)) as TOTALREVENUE,
                          RS.CONSTITUENTID,
                          RS.REVENUEID
                from (
                        select
                                sum(RS.AMOUNTINCURRENCY) as  AMOUNTINCURRENCY,
                                RS.CONSTITUENTID,
                                RS.REVENUEID
                         from 
                                dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK (@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS
                                inner join DESIGNATIONS_CTE D on RS.DESIGNATIONID = D.ID
                        where (RS.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                (RS.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                (RS.TRANSACTIONTYPECODE in (1,3,4,6,7,8) or (RS.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,3,4)))
                        group by
                            RS.CONSTITUENTID, RS.REVENUEID
                    ) RS
                    left outer join (SELECT SUM(AMOUNTINCURRENCY) AMOUNTINCURRENCY, REVENUEID  
                                         from UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK (@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) WO
                                              inner join DESIGNATIONS_CTE D on WO.DESIGNATIONID = D.ID
                                              group by revenueid ) WO on RS.REVENUEID = WO.REVENUEID
            ) as DATA


);