USP_DATALIST_GOALVSACTUALNUMBERS

Detailed Goal Vs Actual Numbers
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_GOALVSACTUALNUMBERS(@EVENTID uniqueidentifier)
as
  set nocount on;

  declare @SPONSORSHIPGOALCOUNT int;
  declare @PARTICIPANTGOALCOUNT int;
  declare @TEAMGOALCOUNT int;
  declare @COMPANYGOALCOUNT int;
  declare @SPONSORSHIPACTUALCOUNT int;
  declare @PARTICIPANTACTUALCOUNT int;
  declare @TEAMACTUALCOUNT int;
  declare @COMPANYACTUALCOUNT int;
  declare @DetailedCount table
  (
    COUNTTYPENAME varchar(50),
    GOALCOUNT int,
    ACTUALCOUNT int
  );

  select @SPONSORSHIPGOALCOUNT=RECRUITMENTSPONSOR,@PARTICIPANTGOALCOUNT=RECRUITMENTINDIVIDUAL,@TEAMGOALCOUNT=RECRUITMENTTEAM,@COMPANYGOALCOUNT=RECRUITMENTCOMPANY 
  from dbo.EVENTGOAL(nolock)
  where EVENTID = @EVENTID;

  -- get sponsorship actual count, @SPONSORSHIPACTUALCOUNT

  select @SPONSORSHIPACTUALCOUNT = count(ID)
  from dbo.EVENTSPONSOR (nolock)
  where EVENTID = @EVENTID;

  -- get participant actual count, @PARTICIPANTACTUALCOUNT

  select @PARTICIPANTACTUALCOUNT = count(ID)
  from dbo.REGISTRANT(nolock)
  where EVENTID = @EVENTID;

  -- get team actual count, @TEAMACTUALCOUNT

  select @TEAMACTUALCOUNT = count(TEAMFUNDRAISINGTEAMID)
  from dbo.TEAMEXTENSION(nolock)
  where EVENTID = @EVENTID and TYPECODE = 1;

  -- get company actual count, @COMPANYACTUALCOUNT

  select @COMPANYACTUALCOUNT = count(TEAMFUNDRAISINGTEAMID)
  from dbo.TEAMEXTENSION(nolock)
  where EVENTID = @EVENTID and TYPECODE = 2;

  insert into  @DetailedCount(COUNTTYPENAME, GOALCOUNT, ACTUALCOUNT) 
  values('Corporate sponsorship count',@SPONSORSHIPGOALCOUNT,@SPONSORSHIPACTUALCOUNT);

  insert into  @DetailedCount(COUNTTYPENAME, GOALCOUNT, ACTUALCOUNT) 
  values('Participant count',@PARTICIPANTGOALCOUNT,@PARTICIPANTACTUALCOUNT);

  insert into  @DetailedCount(COUNTTYPENAME, GOALCOUNT, ACTUALCOUNT) 
  values('Team count',@TEAMGOALCOUNT,@TEAMACTUALCOUNT);

  insert into  @DetailedCount(COUNTTYPENAME, GOALCOUNT, ACTUALCOUNT) 
  values('Company fundraising </br>count',@COMPANYGOALCOUNT,@COMPANYACTUALCOUNT);

  select COUNTTYPENAME,GOALCOUNT,ACTUALCOUNT from @DetailedCount;