USP_REPORT_VSECOLLEGE_SECTION4B_PART2

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

                declare @RESULTS table
                (
                    LABEL nvarchar(150),
                    LARGEST money,
                    SECOND_LARGEST money,
                    THIRD_LARGEST money
                );

                declare @TOTALS table
                (
                    TOTAL money,
                    CONSTITUENTID uniqueidentifier
                );

                declare @CONSTITUENTID1 uniqueidentifier;
                declare @CONSTITUENTID2 uniqueidentifier;

                insert into @RESULTS
                (LABEL)
                values
                ('1. Foundations');

                insert into @TOTALS
                    select top 3
                        sum(REPORT_VSECOLLEGE.REVENUESPLITAMOUNT) as TOTAL_AMOUNT,
                        REPORT_VSECOLLEGE.CONSTITUENTID as CONSTITUENTID
                    from
                        (
                        select
                            REPORT_VSECOLLEGE.REVENUESPLITAMOUNT,
                            REPORT_VSECOLLEGE.CONSTITUENTID
                        from
                            dbo.UFN_REPORT_VSECOLLEGE(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                        where
                            REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (12, 13)

                        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
                        from
                            dbo.UFN_REPORT_VSECOLLEGE_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSECOLLEGE
                        where
                            REPORT_VSECOLLEGE.VSECONSTITUENCYCODE in (12, 13)

                        ) REPORT_VSECOLLEGE
                    group by REPORT_VSECOLLEGE.CONSTITUENTID
                    order by TOTAL_AMOUNT desc

                update @RESULTS
                set
                    LARGEST = TOTALS.TOTAL,
                    @CONSTITUENTID1 = TOTALS.CONSTITUENTID
                from 
                    (select top 1 *    from @TOTALS) as TOTALS

                update @RESULTS
                set
                    SECOND_LARGEST = TOTALS.TOTAL,
                    @CONSTITUENTID2 = TOTALS.CONSTITUENTID
                from 
                    (select top 1 *    from @TOTALS where CONSTITUENTID <> @CONSTITUENTID1) as TOTALS

                update @RESULTS
                set
                    THIRD_LARGEST = TOTALS.TOTAL
                from 
                    (select top 1 *    from @TOTALS where CONSTITUENTID <> @CONSTITUENTID1 and CONSTITUENTID <> @CONSTITUENTID2) as TOTALS

                select
                    LABEL,
                    coalesce(LARGEST, 0) as LARGEST,
                    coalesce(SECOND_LARGEST, 0) as SECOND_LARGEST,
                    coalesce(THIRD_LARGEST, 0) as THIRD_LARGEST
                from
                    @RESULTS;