USP_REPORT_VSESECONDARY_SECTION3B

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

                -- Writing the query this way as opposed to how it's written in 3A

                -- is for optimization purposes. So, unless it's causing problems

                -- please leave it like this. - DavidHe



                declare @REPORT_VSESECONDARY table
                (
                    VSECATEGORYCODE tinyint,
                    ALUMNI money,
                    PARENTS money,
                    GRANDPARENTS money,
                    OTHERINDIVIDUALS money,
                    FOUNDATIONS money,
                    CORPORATIONS money,
                    RELIGIOUSORGANIZATIONS money,
                    FUNDRAISINGCONSORTIA money,
                    OTHERORGANIZATIONS money
                );

                insert into @REPORT_VSESECONDARY
                (
                    VSECATEGORYCODE,
                    ALUMNI,
                    PARENTS,
                    GRANDPARENTS,
                    OTHERINDIVIDUALS,
                    FOUNDATIONS,
                    CORPORATIONS,
                    RELIGIOUSORGANIZATIONS,
                    FUNDRAISINGCONSORTIA,
                    OTHERORGANIZATIONS
                )
                (
                    select  
                        REPORT_VSESECONDARY.VSECATEGORYCODE,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (0) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as ALUMNI,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (5, 6) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as PARENTS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (7) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as GRANDPARENTS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (8, 9, 10, 11) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as OTHERINDIVIDUALS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (12, 13) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as FOUNDATIONS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (14) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as CORPORATIONS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (15) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as RELIGIOUSORGANIZATIONS,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (16) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as FUNDRAISINGCONSORTIA,
                        coalesce(sum(case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE in (17) and REPORT_VSESECONDARY.REVENUETYPE = 0 then REPORT_VSESECONDARY.REVENUESPLITAMOUNT else 0 end), 0) as OTHERORGANIZATIONS
                    from
                        dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) REPORT_VSESECONDARY
                    where
                        REPORT_VSESECONDARY.VSECATEGORYCODE in (12, 13, 14, 15, 16)
                    group by REPORT_VSESECONDARY.VSECATEGORYCODE
                );
                select  
                    case VSECATEGORY.VSECATEGORYCODE
                            when 12 then 12
                            when 13 then 13
                            when 15 then 15
                            else 14
                        end as VSECATEGORYCODE,
                        case VSECATEGORY.VSECATEGORYCODE
                            when 12 then '1. Prop., Build. & Equipment'
                            when 13 then '2. Endowment - Unrestricted'
                            when 15 then '4. Loan Funds'
                            else '3. Endowment - Restricted'
                        end as LABEL,
                        coalesce(ALUMNI, 0) ALUMNI,
                        coalesce(PARENTS, 0) PARENTS,
                        coalesce(GRANDPARENTS, 0) GRANDPARENTS,
                        coalesce(OTHERINDIVIDUALS, 0) OTHERINDIVIDUALS,
                        coalesce(FOUNDATIONS, 0) FOUNDATIONS,
                        coalesce(CORPORATIONS, 0) CORPORATIONS,
                        coalesce(RELIGIOUSORGANIZATIONS, 0) RELIGIOUSORGANIZATIONS,
                        coalesce(FUNDRAISINGCONSORTIA, 0) FUNDRAISINGCONSORTIA,
                        coalesce(OTHERORGANIZATIONS, 0) OTHERORGANIZATIONS
                from
                    dbo.VSECATEGORY
                left outer join @REPORT_VSESECONDARY REPORT_VSESECONDARY on REPORT_VSESECONDARY.VSECATEGORYCODE = VSECATEGORY.VSECATEGORYCODE
                where
                    VSECATEGORY.USAGETYPECODE <> 0