USP_DATALIST_FAFHOUSEHOLDMONEY_UNITPERFORMANCE

Data list for FAF household money unit performance.
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


CREATE procedure dbo.USP_DATALIST_FAFHOUSEHOLDMONEY_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),
        GROUPTOTAL money,
        ORDERNUMBER int
    );

    -- total<50

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$1-$50'
        , ISNULL(SUM(TOTAL), 0)
        , '1'
    from @TEMP_HOUSEHOLDTOTAL HT
    where HT.TOTAL < 50;

    -- 50<total<=250

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$51-$250'
        , ISNULL(SUM(TOTAL), 0)
        , '2'
    from @TEMP_HOUSEHOLDTOTAL HT 
    where HT.TOTAL > 50 AND HT.TOTAL <= 250;

    -- 250<total<=500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$251-$500'
        , ISNULL(SUM(TOTAL), 0)
        , '3'
    from @TEMP_HOUSEHOLDTOTAL HT 
    where HT.TOTAL > 250 AND HT.TOTAL <= 500;

    -- 500<total<=1000

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$500-$1000'
        , ISNULL(SUM(TOTAL), 0)
        , '4'
    from @TEMP_HOUSEHOLDTOTAL HT 
    where HT.TOTAL > 500 AND HT.TOTAL <= 1000;

    -- 1000<total<=2500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$1000-$2500'
        , ISNULL(SUM(TOTAL), 0)
        , '5'
    from @TEMP_HOUSEHOLDTOTAL HT 
    where HT.TOTAL > 1000 AND HT.TOTAL <= 2500;

  -- total>2500

    insert into @TEMP_HOUSEHOLDGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$2501+'
        , ISNULL(SUM(TOTAL), 0)
        , '6'
    from @TEMP_HOUSEHOLDTOTAL HT 
    where HT.TOTAL > 2500;

    select NAME
        ,GROUPTOTAL
    from @TEMP_HOUSEHOLDGROUPTOTAL
    order by ORDERNUMBER