USP_REPORT_VSESECONDARY_SECTION4D_PART1

            CREATE procedure dbo.USP_REPORT_VSESECONDARY_SECTION4D_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 @RESULTS table
                (
                    ROWNUMBER int,
                    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
                (ROWNUMBER, LABEL)
                values
                (1, '1. From living individuals');

                insert into @RESULTS
                (ROWNUMBER, LABEL)
                values
                (2, '2. Through estate settlements (bequests)');

                insert into @TOTALS
                    select top 3
                        sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT) as TOTAL_AMOUNT,
                        REPORT_VSESECONDARY.CONSTITUENTID as CONSTITUENTID
                    from
                        (
                        select
                            REPORT_VSESECONDARY.REVENUESPLITAMOUNT,
                            REPORT_VSESECONDARY.CONSTITUENTID
                        from
                            dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                        where
                            REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                            and (REPORT_VSESECONDARY.VEHICLECODE is null or REPORT_VSESECONDARY.VEHICLECODE <> 10)
                            and REPORT_VSESECONDARY.DECEASEDCONSTITUENTID is null

                        union all

                        select
                            case when REPORT_VSESECONDARY.FACEVALUE = 0 then 0 else (REPORT_VSESECONDARY.PRESENTVALUE * (REPORT_VSESECONDARY.PLANNEDGIFTDESIGNATIONAMOUNT / REPORT_VSESECONDARY.FACEVALUE)) end as REVENUESPLITAMOUNT,
                            REPORT_VSESECONDARY.CONSTITUENTID
                        from
                            dbo.UFN_REPORT_VSESECONDARY_PLANNEDGIFT(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                        where
                            REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                            and REPORT_VSESECONDARY.DECEASEDCONSTITUENTID is null

                        ) REPORT_VSESECONDARY
                    group by REPORT_VSESECONDARY.CONSTITUENTID
                    order by TOTAL_AMOUNT desc

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

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

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

                delete from @TOTALS

                insert into @TOTALS
                    select top 3
                        sum(REPORT_VSESECONDARY.REVENUESPLITAMOUNT) as TOTAL_AMOUNT,
                        REPORT_VSESECONDARY.CONSTITUENTID as CONSTITUENTID
                    from
                        dbo.UFN_REPORT_VSESECONDARY(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, @USEGIFTDATE) as REPORT_VSESECONDARY
                    where
                        REPORT_VSESECONDARY.VSECONSTITUENTTYPECODE = 0
                        and REPORT_VSESECONDARY.VEHICLECODE = 10
                    group by REPORT_VSESECONDARY.CONSTITUENTID
                    order by TOTAL_AMOUNT desc

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

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

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

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