USP_REPORT_VSESECONDARY_SECTION4D_PART2

Returns VSE secondary report Section 4d part 2 data.
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@INCLUDEINACTIVE	smallint	IN
@INCLUDEDECEASED	smallint	IN
@USEGIFTDATE	smallint	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_VSESECONDARY_SECTION4D_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. Corporations');

                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.VSECONSTITUENCYCODE = 14
                    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

                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;