USP_REPORT_VSECOLLEGE_SECTION4E_PART1

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

                select
                    '1. Include current, emeritus and honorary board members' as LABEL,
                    coalesce(NUMBER_DONORS, 0) as NUMBER_DONORS,
                    coalesce(TOTAL_AMOUNT, 0) as TOTAL_AMOUNT
                from
                    dbo.VSECONSTITUENCY
                    left outer join
                    (
                        select
                            count(distinct REPORT_VSECOLLEGE.CONSTITUENTID) as NUMBER_DONORS,
                            sum(REPORT_VSECOLLEGE.REVENUESPLITAMOUNT) as TOTAL_AMOUNT,
                            REPORT_VSECOLLEGE.VSECONSTITUENCYCODE as VSECONSTITUENCYCODE
                        from
                            (
                            select
                                REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                            where
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = 10

                            union all

                            select
                                case when REPORT_VSECOLLEGE.FACEVALUE = 0 then 0 else (REPORT_VSECOLLEGE.PRESENTVALUE * (REPORT_VSECOLLEGE.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSECOLLEGE.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                            where
                                REPORT_VSECOLLEGE.VSECONSTITUENCYCODE = 10
                            ) REPORT_VSECOLLEGE
                        group by REPORT_VSECOLLEGE.VSECONSTITUENCYCODE
                    ) as DATA on VSECONSTITUENCY.VSECONSTITUENCYCODE = DATA.VSECONSTITUENCYCODE
                where
                    VSECONSTITUENCY.VSECONSTITUENCYCODE = 10;