USP_DATALIST_WP_GETGIVINGTOTALS

CREATE procedure dbo.USP_DATALIST_WP_GETGIVINGTOTALS(
    @CONSTITUENTID uniqueidentifier = null,
    @CONFIDENCE integer = 0
    )
as
    set nocount on;
declare @tempCategories table (
    CATEGORY nvarchar(201),
    VALUE integer
)

insert into @tempCategories
    select 
        PHILANTHROPICGIFTCATEGORY.DESCRIPTION as [CATEGORY],
        COUNT(*) as [VALUE]
    from WPPHILANTHROPICGIFT as wp
    left outer join WPPHILANTHROPICGIFTCATEGORY 
        on wp.ID = WPPHILANTHROPICGIFTCATEGORY.WPPHILANTHROPICGIFTID
    left outer join PHILANTHROPICGIFTCATEGORY  
        on PHILANTHROPICGIFTCATEGORY.ID = WPPHILANTHROPICGIFTCATEGORY.PHILANTHROPICGIFTCATEGORYID
    left outer join dbo.MATCHCODE 
        on wp.MC = MATCHCODE.MATCHCODE
    left outer join dbo.WEALTHSOURCE 
        on wp.SOURCE = WEALTHSOURCE.SOURCE
    left outer join dbo.CONFIDENCERATING 
        on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID 
        and CONFIDENCERATING.WEALTHSOURCEID = WEALTHSOURCE.ID
    where 
        wp.WEALTHID = @CONSTITUENTID
        and (select
            case
                when wp.CONFIRMED = 1 then '99'
                else
                    ISNULL(coalesce(CONFIDENCERATING.CONFIDENCE, MATCHCODE.DEFAULTCONFIDENCE),
                        (select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
            end
        ) >= @CONFIDENCE
        and wp.REJECTED = 0
        and PHILANTHROPICGIFTCATEGORY.DESCRIPTION is not null
        group by PHILANTHROPICGIFTCATEGORY.DESCRIPTION

declare @numCategories integer = (select COUNT(*) from @tempCategories);    

if @numCategories < 7 
begin
    select CATEGORY, VALUE, 0 as [ADDITIONALCATEGORY] from @tempCategories
        Order by VALUE desc, CATEGORY
end
else begin

    declare @tempCategories2 table (
        CATEGORY nvarchar(201),
        VALUE integer
    )

    insert into @tempCategories2
    select top 5 CATEGORY, VALUE from @tempCategories    
        Order by VALUE desc, CATEGORY

    declare @TOTAL integer;
    select @TOTAL = SUM(VALUE) from @tempCategories    
    declare @TOP5TOTAL integer;
    select @TOP5TOTAL = SUM(VALUE) from @tempCategories2    

    select CATEGORY, VALUE, 0 as [ADDITIONALCATEGORY] from @tempCategories2
    UNION ALL
    select 'Additional Categories', (@TOTAL-@TOP5TOTAL), 1
        Order by ADDITIONALCATEGORY, VALUE desc, CATEGORY
end