USP_REPORT_VSECOLLEGE_SECTION4B_PART3

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

                select 
                    case VSECATEGORY.VSECATEGORYCODE
                        when 9 then 1
                        when 17 then 2
                        when 25 then 2
                    end as VSECATEGORYCODE,
                    case VSECATEGORY.VSECATEGORYCODE
                        when 9 then '1. Restricted to current operations'
                        when 17 then '2. Restricted to capital purposes'
                        when 25 then '2. Restricted to capital purposes'
                    end as LABEL,
                    coalesce(REPORT_VSECOLLEGE.NUMBER_DONORS, 0) as NUMBER_DONORS,
                    coalesce(REPORT_VSECOLLEGE.AMOUNT, 0) as AMOUNT
                from
                    dbo.VSECATEGORY
                    left outer join 
                    (
                        select 
                            count(distinct REPORT_VSECOLLEGE.CONSTITUENTID) as NUMBER_DONORS,
                            sum(REPORT_VSECOLLEGE.REVENUESPLITAMOUNT) as AMOUNT,
                            REPORT_VSECOLLEGE.VSECATEGORYCODE
                        from 
                            (
                            select
                                REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
                                REPORT_VSECOLLEGE.CONSTITUENTID,
                                case when VSECATEGORYCODE = 25 then 17 else VSECATEGORYCODE end VSECATEGORYCODE
                            from
                                dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                            where
                                REPORT_VSECOLLEGE.VSECATEGORYCODE in (9, 17, 25)

                            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,
                                case when VSECATEGORYCODE = 25 then 17 else VSECATEGORYCODE end VSECATEGORYCODE
                            from
                                dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                            where
                                REPORT_VSECOLLEGE.VSECATEGORYCODE in (9, 17, 25)
                            ) as REPORT_VSECOLLEGE
                        group by VSECATEGORYCODE
                    ) as REPORT_VSECOLLEGE on REPORT_VSECOLLEGE.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.VSECATEGORYCODE in (9, 17, 25);