USP_REPORT_VSESECONDARY_SECTION4E_PART1

            CREATE procedure dbo.USP_REPORT_VSESECONDARY_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;

                declare @NUMBER_MEMBERS int;
                declare @NUMBER_DONORS int;
                declare @AMOUNT_CURRENTOPERATIONS money;
                declare @AMOUNT_CAPITALPURPOSES money;

                select 
                    @NUMBER_MEMBERS = coalesce(VSECNTS.NUMRECORDS, 0)
                from
                    dbo.VSECONSTITUENCY
                    left outer join    dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 0) VSECNTS 
                        on VSECNTS.VSECONSTITUENCYCODE = 10 and VSECNTS.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                where
                    VSECONSTITUENCY.VSECONSTITUENCYCODE = 10
                    and VSECONSTITUENCY.USAGETYPECODE <> 0;

                select
                    @NUMBER_DONORS = coalesce(V2.NUMDONOR, 0)
                from 
                    dbo.VSECONSTITUENCY
                    left outer join 
                    (
                        select 
                            count(distinct REPORT_VSESECONDARY.CONSTITUENTID) as NUMDONOR,
                            REPORT_VSESECONDARY.VSECONSTITUENCYCODE
                        from
                            (
                            select 
                                CONSTITUENTID,
                                VSECONSTITUENCYCODE
                            from 
                                dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10

                            union all

                            select 
                                CONSTITUENTID,
                                VSECONSTITUENCYCODE
                            from 
                                dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10
                            ) REPORT_VSESECONDARY
                        group by REPORT_VSESECONDARY.VSECONSTITUENCYCODE
                    ) V2 on V2.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                where
                    VSECONSTITUENCY.VSECONSTITUENCYCODE = 10
                    and VSECONSTITUENCY.USAGETYPECODE <> 0;

                select
                    @AMOUNT_CURRENTOPERATIONS = coalesce(V2.AMOUNT, 0)
                from
                    dbo.VSECONSTITUENCY
                    left outer join 
                    (
                        select
                            sum(REVENUESPLITAMOUNT) as AMOUNT,
                            VSECONSTITUENCYCODE
                        from
                            (
                            select
                                REVENUESPLITAMOUNT,
                                VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10
                                and REPORT_VSESECONDARY.VSECATEGORYPURPOSETYPECODE = 0

                            union all

                            select
                                case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10
                                and REPORT_VSESECONDARY.VSECATEGORYPURPOSETYPECODE = 0
                            ) REPORT_VSESECONDARY
                        group by VSECONSTITUENCYCODE
                    ) V2 on V2.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                where
                    VSECONSTITUENCY.VSECONSTITUENCYCODE = 10
                    and VSECONSTITUENCY.USAGETYPECODE <> 0;

                select
                    @AMOUNT_CAPITALPURPOSES = coalesce(V2.AMOUNT, 0)
                from
                    dbo.VSECONSTITUENCY
                    left outer join 
                    (
                        select
                            sum(REVENUESPLITAMOUNT) as AMOUNT,
                            VSECONSTITUENCYCODE
                        from
                            (
                            select
                                REVENUESPLITAMOUNT,
                                VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10
                                and REPORT_VSESECONDARY.VSECATEGORYPURPOSETYPECODE = 1

                            union all

                            select
                                case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end as REVENUESPLITAMOUNT,
                                VSECONSTITUENCYCODE
                            from
                                dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                            where
                                REPORT_VSESECONDARY.VSECONSTITUENCYCODE = 10
                                and REPORT_VSESECONDARY.VSECATEGORYPURPOSETYPECODE = 1
                            ) REPORT_VSESECONDARY
                        group by VSECONSTITUENCYCODE
                    ) V2 on V2.VSECONSTITUENCYCODE = VSECONSTITUENCY.VSECONSTITUENCYCODE
                where
                    VSECONSTITUENCY.VSECONSTITUENCYCODE = 10
                    and VSECONSTITUENCY.USAGETYPECODE <> 0;

                select
                    coalesce(@NUMBER_MEMBERS, 0) as NUMBER_MEMBERS,
                    coalesce(@NUMBER_DONORS, 0) as NUMBER_DONORS,
                    coalesce(@AMOUNT_CURRENTOPERATIONS, 0) as AMOUNT_CURRENTOPERATIONS,
                    coalesce(@AMOUNT_CAPITALPURPOSES, 0) as AMOUNT_CAPITALPURPOSES;