USP_FEPSUBMITPROCESS_GETDATAFOREXPORT

This stored procedure will retrieve information needed for the export file of the fundraising effectiveness project submit process.

Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@SITEID	uniqueidentifier	IN
@NUMGIFTS	int	INOUT
@LARGESTGIFT	decimal(18, 0)	INOUT

Definition

Copy



create procedure dbo.USP_FEPSUBMITPROCESS_GETDATAFOREXPORT
(
    @STARTDATE datetime,
    @ENDDATE datetime,
    @SITEID uniqueidentifier = null,
    @NUMGIFTS integer = 0 output,
    @LARGESTGIFT decimal = 0 output
)
as
begin

    declare @REVENUE table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        DATE datetime
    )
    insert into @REVENUE
        select
            REVENUE.ID,
            REVENUE.CONSTITUENTID,
            sum(REVENUESPLIT.AMOUNT) [AMOUNT],
            REVENUE.DATE            
        from dbo.REVENUE
        inner join dbo.REVENUESPLIT 
            on REVENUE.ID = REVENUESPLIT.REVENUEID
        inner join dbo.CONSTITUENT
            on REVENUE.CONSTITUENTID = CONSTITUENT.ID
        where REVENUE.TRANSACTIONTYPECODE = 0            -- all donations, regardless of application type

            and REVENUE.DATE between @STARTDATE and @ENDDATE
            and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(REVENUESPLIT.DESIGNATIONID))

        group by REVENUE.ID, REVENUE.CONSTITUENTID, REVENUE.TRANSACTIONTYPECODE, REVENUE.DATE;

    select
        @NUMGIFTS = count(distinct REVENUEID)
    from @REVENUE;

    declare @CONSTITUENTREVENUE table (
        CONSTITUENTID uniqueidentifier,
        AMOUNT money
    )
    insert into @CONSTITUENTREVENUE (CONSTITUENTID, AMOUNT)
        select
            CONSTITUENTID,
            sum(AMOUNT)
        from @REVENUE
        group by CONSTITUENTID;

    select 
        @LARGESTGIFT = max(AMOUNT)
    from @CONSTITUENTREVENUE;

    select @NUMGIFTS, @LARGESTGIFT;

end