USP_DATALIST_FAFREGISTRANTMONEY_UNITPERFORMANCE

Data list for FAF registrant 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_FAFREGISTRANTMONEY_UNITPERFORMANCE(@EVENTID uniqueidentifier)
as
    set nocount on;

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

  insert into @TEMP_REGISTRANTTOTAL
    (        
        ID, TOTAL
    )
  select 
    ER.ID,
    ISNULL(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(ER.ID, ER.EVENTID), 0) as [TOTAL]
  from REGISTRANT ER 
  where ER.EVENTID = @EVENTID;

    declare  @TEMP_REGISTRANTGROUPTOTAL as table
    (        
        NAME nvarchar(100),
        GROUPTOTAL money,
        ORDERNUMBER int
    );

    -- total<=50

    insert into @TEMP_REGISTRANTGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$1-$50'
        , ISNULL(SUM(TOTAL), 0)
        , '1'
    from @TEMP_REGISTRANTTOTAL RT 
    where RT.TOTAL <= 50;

    -- 50<total<=250

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

    -- 250<total<=500

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

    -- 500<total<=1000

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

    -- total>1000

    insert into @TEMP_REGISTRANTGROUPTOTAL
    (        
        NAME, GROUPTOTAL, ORDERNUMBER
    )
    select 
        '$1001+'
        , ISNULL(SUM(TOTAL), 0)
        , '4'
    from @TEMP_REGISTRANTTOTAL RT 
    where RT.TOTAL > 1000;

    select NAME
        ,GROUPTOTAL
    from @TEMP_REGISTRANTGROUPTOTAL
    order by ORDERNUMBER