USP_DATALIST_FAFHOUSEHOLDCOUNT_UNITPERFORMANCE

CREATE procedure dbo.USP_DATALIST_FAFHOUSEHOLDCOUNT_UNITPERFORMANCE(@EVENTID uniqueidentifier)
as
    set nocount on;

    declare @TEMP_HOUSEHOLDTOTAL as table
    (        
        ID uniqueidentifier,
        TOTAL money
    );

  insert into @TEMP_HOUSEHOLDTOTAL
    (        
        ID, TOTAL
    )
  select
    TE.TEAMFUNDRAISINGTEAMID, 
    dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TE.TEAMFUNDRAISINGTEAMID, TE.EVENTID) as TOTAL
  from TEAMEXTENSION TE(nolock)
  where TE.TYPECODE=3
  and TE.EVENTID = @EVENTID;

    declare  @TEMP_HOUSEHOLDGROUPTOTAL as table
    (        
        NAME nvarchar(100),
        NUMBERCOUNT int,
        ORDERNUMBER int
    );

    -- total=0

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$0'
        , COUNT(*)
        , '1'
    from @TEMP_HOUSEHOLDTOTAL TH
    where TH.TOTAL = 0;

    -- total<50

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$1-$50'
        , COUNT(*)
        , '2'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 0 AND TH.TOTAL <= 50;

    -- 50<total<=250

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$51-$250'
        , COUNT(*)
        , '3'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 50 AND TH.TOTAL <= 250;

    -- 251<=total<500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$251-$500'
        , COUNT(*)
        , '4'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 250 AND TH.TOTAL <= 500;

    -- 500<total<1000

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$500-$1000'
        , COUNT(*)
        , '5'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 500 AND TH.TOTAL <= 1000;

    -- 1000<total<2500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$1000-$2500'
        , COUNT(*)
        , '6'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 1000 AND TH.TOTAL <= 2500;

  -- total>2500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$2501+'
        , COUNT(*)
        , '7'
    from @TEMP_HOUSEHOLDTOTAL TH 
    where TH.TOTAL > 2500;

    select NAME
        ,NUMBERCOUNT
    from @TEMP_HOUSEHOLDGROUPTOTAL
    order by ORDERNUMBER