USP_REPORT_VSESECONDARY_SECTION4C_PART2

Returns VSE secondary report Section 4c part 2 data.
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@INCLUDEINACTIVE	smallint	IN
@INCLUDEDECEASED	smallint	IN
@USEGIFTDATE	smallint	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_VSESECONDARY_SECTION4C_PART2
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDEINACTIVE smallint = 0,
                @INCLUDEDECEASED smallint = 0,
                @USEGIFTDATE smallint = 0
            )
            with execute as owner
            as
                set nocount on;

                declare @RESULTS table
                (
                    LABEL nvarchar(150),
                    NUMBER_GIFTS int,
                    AMOUNT money
                );

                insert into @RESULTS
                (LABEL, NUMBER_GIFTS, AMOUNT)
                (
                    select
                        '1. Cash and securities (exclusive of matching gifts)',
                        coalesce(count(distinct REPORT_VSESECONDARY.REVENUEID), 0),
                        coalesce(sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT), 0)
                    from
                        dbo.VSECONSTITUENCY
                        left outer join dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            on REPORT_VSESECONDARY.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REPORT_VSESECONDARY.REVENUEID
                    where
                        VSECONSTITUENCY.VSECONSTITUENCYCODE = 14
                        -- Not a matching gift payment or donor challenge

                        and REPORT_VSESECONDARY.APPLICATIONCODE <> 7 and REPORT_VSESECONDARY.APPLICATIONCODE <> 13
                        -- Not a payment method of property

                        and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 5
                );

                insert into @RESULTS
                (LABEL, NUMBER_GIFTS, AMOUNT)
                (
                    select
                        '2. Other company property',
                        coalesce(count(distinct REPORT_VSESECONDARY.REVENUEID), 0),
                        coalesce(sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT), 0)
                    from
                        dbo.VSECONSTITUENCY
                        left outer join dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            on REPORT_VSESECONDARY.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REPORT_VSESECONDARY.REVENUEID
                    where
                        VSECONSTITUENCY.VSECONSTITUENCYCODE = 14
                        -- Not a matching gift payment or donor challenge

                        and REPORT_VSESECONDARY.APPLICATIONCODE <> 7 and REPORT_VSESECONDARY.APPLICATIONCODE <> 13
                        -- Is a payment method of property

                        and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5
                );

                insert into @RESULTS
                (LABEL, NUMBER_GIFTS, AMOUNT)
                (
                    select
                        '3. Matching gifts',
                        coalesce(count(distinct REPORT_VSESECONDARY.REVENUEID), 0),
                        coalesce(sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT), 0)
                    from
                        dbo.VSECONSTITUENCY
                        left outer join dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            on REPORT_VSESECONDARY.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                    where
                        VSECONSTITUENCY.VSECONSTITUENCYCODE = 14
                        -- Matching gift payment or donor challenge

                        and (REPORT_VSESECONDARY.APPLICATIONCODE = 7 or REPORT_VSESECONDARY.APPLICATIONCODE = 13)
                );

                select
                    LABEL,
                    NUMBER_GIFTS,
                    AMOUNT
                from
                    @RESULTS;