USP_DATALIST_FAFCOMPANYCOUNT_UNITPERFORMANCE

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

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

  insert into @TEMP_COMPANYTOTAL
    (        
        ID, TOTAL
    )
  select
    TE.TEAMFUNDRAISINGTEAMID, 
    dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TE.TEAMFUNDRAISINGTEAMID, TE.EVENTID) as TOTAL
  from TEAMEXTENSION TE(nolock)
  where TE.TYPECODE = 2
  and TE.EVENTID = @EVENTID;

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

    -- total=0

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$0'
        , COUNT(*)
        , '1'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL = 0;

    -- total<500

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$1-$500'
        , COUNT(*)
        , '2'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 0 AND CT.TOTAL <= 500;

    -- 500<total<=5000

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$501-$5000'
        , COUNT(*)
        , '3'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 500 AND CT.TOTAL <= 5000;

    -- 5000<=total<25000

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$5001-$25000'
        , COUNT(*)
        , '4'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 5000 AND CT.TOTAL <= 25000;

    -- 25000<total<=50000

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$25001-$50000'
        , COUNT(*)
        , '5'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 25000 AND CT.TOTAL <= 50000;

  -- 50000<total<=100000

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$50001-$100000'
        , COUNT(*)
        , '5'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 50000 AND CT.TOTAL <= 100000;

    -- total>100000

    insert into @TEMP_COMPANYGROUPTOTAL
    (        
        NAME, NUMBERCOUNT, ORDERNUMBER
    )
    select 
        '$100001+'
        , COUNT(*)
        , '6'
    from @TEMP_COMPANYTOTAL CT 
    where CT.TOTAL > 100000;

    select NAME
        ,NUMBERCOUNT
    from @TEMP_COMPANYGROUPTOTAL
    order by ORDERNUMBER