USP_REPORT_VSESECONDARY_SECTION4A_PART1

            CREATE procedure dbo.USP_REPORT_VSESECONDARY_SECTION4A_PART1
            (
                @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
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 then 1
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 5 then 2
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 6 then 2
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 7 then 3
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 8 then 4
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 9 then 5
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 10 then 6
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 11 then 6
                        else VSECONSTITUENCY.VSECONSTITUENCYCODE
                    end as VSECONSTITUENCYCODE,
                    case
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 0 then '1. Alumni'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 5 then '2. Parents'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 6 then '2. Parents'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 7 then '3. Grandparents'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 8 then '4. Faculty and Staff'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 9 then '5. Students'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 10 then '6. Other Individuals'
                        when VSECONSTITUENCY.VSECONSTITUENCYCODE = 11 then '6. Other Individuals'
                    end as LABEL,
                    isnull(VSECNTS.NUMRECORDS, 0) as NUMBER_RECORDS,
                    isnull(VSECNTS.NUMSOLICIT, 0) as NUMBER_SOLICITED,
                    isnull(V2.NUMDONOR, 0) as NUMBER_DONORS,
                    isnull(V2.TOTAMT, 0) as TOTAL_AMOUNT
                from 
                    dbo.VSECONSTITUENCY
                    left outer join
                    (
                        select
                            count(distinct REPORT_VSESECONDARY.CONSTITUENTID) as NUMDONOR,
                            sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT) as TOTAMT,
                            REPORT_VSESECONDARY.VSECONSTITUENCYCODE
                        from
                            (
                            select
                                REPORT_VSESECONDARY.CONSTITUENTID,
                                REPORT_VSESECONDARY.REVENUESPLITAMOUNT,
                                case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 6 then 5 when REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 11 then 10 else REPORT_VSESECONDARY.VSECONSTITUENCYCODE end as VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                            union all

                            select
                                REPORT_VSESECONDARY.CONSTITUENTID,
                                case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                case when REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 6 then 5 when REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 11 then 10 else REPORT_VSESECONDARY.VSECONSTITUENCYCODE end as VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                            ) REPORT_VSESECONDARY
                        group by REPORT_VSESECONDARY.VSECONSTITUENCYCODE
                    ) as V2 on V2.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                    left outer join 
                    (
                        select 
                            case when VSECONSTITUENCYCODE = 6 then 5 when VSECONSTITUENCYCODE = 11 then 10 else VSECONSTITUENCYCODE end as VSECONSTITUENCYCODE,
                            sum(numrecords) NUMRECORDS, 
                            sum(numsolicit) NUMSOLICIT,
                            PROGRAMCODE
                        from
                            dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 0)
                        where PROGRAMCODE is null
                        group by (case when VSECONSTITUENCYCODE = 6 then 5 when VSECONSTITUENCYCODE = 11 then 10 else VSECONSTITUENCYCODE end), PROGRAMCODE
                    ) as VSECNTS on VSECNTS.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE 
                where
                    VSECONSTITUENCY.TYPECODE = 0
                    and VSECONSTITUENCY.USAGETYPECODE <> 0

                union all
                select 1, '1. Alumni', 0, 0, 0, 0