USP_DATALIST_FUNDRAISINGGROUPMERGEDATAPREVEVT

create procedure dbo.USP_DATALIST_FUNDRAISINGGROUPMERGEDATAPREVEVT
(
  @EVENTID uniqueidentifier,
  @GROUPID uniqueidentifier,
  @ISPREVIOUSEVENT bit = 0 -- used for marketing message merge fields only

)
  as
      set nocount on;

  declare @LYFundraisingGoal money, @LYFundraisingTotal money

    --Get last year data

    select @LYFundraisingGoal=LYRE.FUNDRAISINGGOAL
            ,@LYFundraisingTotal=dbo.UFN_REGISTRANT_GETDONATIONAMOUNT(LYR.ID, @EVENTID, LYTFT.ID, LYTE.TYPECODE, NULL)
    from EVENTEXTENSION EEXT 
    left join dbo.EVENTEXTENSION LYET ON LYET.ID=EEXT.PRIORYEAREVENTID 
    left join dbo.EVENT LYEV ON LYEV.ID=LYET.EVENTID
    left join dbo.TEAMFUNDRAISINGTEAM LYTFT ON LYTFT.APPEALID=LYEV.APPEALID 
    left join dbo.TEAMEXTENSION LYTE ON LYTE.TEAMFUNDRAISINGTEAMID=LYTFT.ID 
    left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN LYTC on LYTFT.ID = LYTC.TEAMFUNDRAISINGTEAMID
    left join dbo.REGISTRANT LYR on LYR.constituentid = LYTC.constituentID and LYR.eventID = LYEV.ID 
    left join dbo.RegistrantExtension LYRE on LYRE.registrantID = LYR.ID
    where LYTE.TEAMCONSTITUENTID=@GROUPID 
    and EEXT.ID=@EVENTID

  select 
  FGLYTotalAmountGoal = isnull(@LYFundraisingGoal,0)
  , FGLYTotalAmountActual = case when ISNULL(@LYFundraisingGoal,0) =0 then 0 else convert(decimal,@LYFundraisingTotal)/@LYFundraisingGoal end
  , FGLYNumberOfParticipantsToRecruit = null
  , FGLYNumberOfParticipantsRecruited = null
  , FGLYPercentageOfDonorsToRetain = null
  , FGLYPercentageOfDonorsRetained = null
  , FGLYNumberOfCommunicationsToSend = null
  , FGLYNumberOfCommunicationsSent = null
  , FGLYNumberOfTeamMembersGoal = null
  , FGLYNumberOfTeamMembersActual = null
  , FGLYPercentageOfTeamMembersToRetain = null
  , FGLYPercentageOfTeamMembersRetained = null
  , FGLYNumberOfTeamsGoal = null
  , FGLYNumberOfTeamsActual = null
  , FGLYPercentageOfTeamsToRetain = null
  , FGLYPercentageOfTeamsRetained = null
  , FGLYNumberOfCompaniesGoal = null
  , FGLYNumberOfCompaniesActual = null
  , FGLYPercentageOfCompaniesToRetain = null
  , FGLYPercentageOfCompaniesRetained = null
  , FGLYGroupTypeName = (CASE WHEN @ISPREVIOUSEVENT = 1 THEN TE.[Type] ELSE PrevTE.TYPE END)

  from dbo.TEAMFUNDRAISINGTEAM T
  inner join dbo.EVENT EV on T.APPEALID = EV.APPEALID 
  inner join dbo.EVENTEXTENSION EEXT on EV.ID = EEXT.EVENTID
  left outer join dbo.TEAMEXTENSION TE on T.ID = TE.TEAMFUNDRAISINGTEAMID 
  left join dbo.EVENTEXTENSION PrevET on PrevET.EVENTID = EEXT.PRIORYEAREVENTID
  left join dbo.TEAMEXTENSION PrevTE on PrevTE.EVENTID = PrevET.EVENTID and PrevTE.TEAMCONSTITUENTID = TE.TEAMCONSTITUENTID
  where T.ID = @GROUPID