USP_REPORT_VSECOLLEGE_SECTION4C_PART1

Returns VSE college 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_VSECOLLEGE_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_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                            on REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                    where
                        REPORT_VSECOLLEGE.VEHICLECODE = 10
                        and VSECATEGORY.PURPOSETYPECODE = 0
                        and VSECATEGORY.USAGETYPECODE <> 1
                        and REPORT_VSECOLLEGE.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_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                        on (REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                            and REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (0, 4, 8, 9, 10, 11))
                    where
                        REPORT_VSECOLLEGE.VEHICLECODE = 10
                        and VSECATEGORY.PURPOSETYPECODE = 1
                        and VSECATEGORY.USAGETYPECODE <> 1
                        and REPORT_VSECOLLEGE.VSECONSTITUENTTYPECODE = 0
                );

                select
                    LABEL,
                    NUMBEROFDONORS,
                    AMOUNT
                from
                    @RESULTS;