USP_REPORT_VSESECONDARY_SECTION4C_PART1

Returns VSE secondary report Section 4c part 1 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_PART1
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDEINACTIVE smallint = 0,
                @INCLUDEDECEASED smallint = 0,
                @USEGIFTDATE smallint = 0
            )
            with execute as caller
            as
                set nocount on;

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

                insert into @RESULTS
                (LABEL, NUMBEROFDONORS, AMOUNT)
                (
                    select 
                        '1. Bequests for Current Operations',
                        isnull(count(distinct CONSTITUENTID), 0),
                        isnull(sum(REVENUESPLITAMOUNT) , 0)
                    from
                        dbo.VSECATEGORY 
                        left outer join dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            on REPORT_VSESECONDARY.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                    where
                        REPORT_VSESECONDARY.VEHICLECODE = 10
                        and VSECATEGORY.PURPOSETYPECODE = 0
                        and VSECATEGORY.USAGETYPECODE <> 0
                        and REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                );

                insert into @RESULTS
                (LABEL, NUMBEROFDONORS, AMOUNT)
                (
                    select 
                        '2. Bequests for Capital Purposes',
                        isnull(count(distinct CONSTITUENTID), 0),
                        isnull(sum(REVENUESPLITAMOUNT),0)
                    from
                        dbo.vsecategory 
                        left outer join dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            on REPORT_VSESECONDARY.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                    where
                        REPORT_VSESECONDARY.VEHICLECODE = 10
                        and VSECATEGORY.PURPOSETYPECODE = 1
                        and VSECATEGORY.USAGETYPECODE <> 0
                        and REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                );

                select
                    LABEL,
                    NUMBEROFDONORS,
                    AMOUNT
                from
                    @RESULTS;