USP_DATALIST_PREVIOUSEVENT

CREATE procedure dbo.USP_DATALIST_PREVIOUSEVENT
        (@EVENTID uniqueidentifier, 
            @CONSTITUENTID uniqueidentifier = null,
            @ROLETYPE tinyint = 1    -- 1: Team, 2: Company, 3: Household

        ) with execute as owner
as

 set nocount on;  

 DECLARE @PRIORYEAREVENTID uniqueidentifier  
 DECLARE @TEAMNAME varchar(200)  
 DECLARE @ISTEAMCAPTAIN bit  
 DECLARE @PARENTTEAMNAME varchar(200)
 DECLARE @TEAMID uniqueidentifier
 DECLARE @PARENTTEAMID uniqueidentifier
 DECLARE @PARENTTEAMTYPECODE integer
 DECLARE @TOPCOMPANYNAME  varchar(200)
 DECLARE @TOPCOMPANYID uniqueidentifier

 Set @TOPCOMPANYNAME = ''
 Set @TOPCOMPANYID = null
 SELECT @PRIORYEAREVENTID = PRIORYEAREVENTID  
 FROM dbo.EVENTEXTENSION WITH (NOLOCK) WHERE EVENTID = @EVENTID  

 IF @CONSTITUENTID IS NOT NULL  
 BEGIN  
   select 
    @TEAMNAME = T.NAME,
    @PARENTTEAMNAME = T2.NAME, 
    @PARENTTEAMTYPECODE = TE2.TYPECODE,
    @ISTEAMCAPTAIN = (CASE WHEN TC.ID IS NOT NULL THEN 1 ELSE 0 END),
    @TEAMID = T.ID,
    @PARENTTEAMID = T.PARENTTEAMID
   FROM  dbo.TEAMEXTENSION TE WITH (NOLOCK)  
   INNER JOIN dbo.TEAMFUNDRAISINGTEAM T WITH (NOLOCK)  
   ON T.ID = TE.TEAMFUNDRAISINGTEAMID  
   AND TE.TYPECODE = @ROLETYPE  
   LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAM T2
   ON T.PARENTTEAMID = T2.id
   LEFT OUTER JOIN dbo.TEAMEXTENSION TE2
   ON T2.id = TE2.TEAMFUNDRAISINGTEAMID
   INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TM WITH (NOLOCK)  
   ON T.ID = TM.TEAMFUNDRAISINGTEAMID  
   AND TM.TEAMFUNDRAISERID IN (SELECT ID from dbo.TEAMFUNDRAISER (nolock) where CONSTITUENTID = @CONSTITUENTID)  
   LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC WITH (NOLOCK)  
   ON TC.TEAMFUNDRAISINGTEAMID = T.ID  
   AND TC.CONSTITUENTID = @CONSTITUENTID  
   WHERE TE.EVENTID = @PRIORYEAREVENTID  

    if @PARENTTEAMTYPECODE = 1 Begin
        Select @TOPCOMPANYNAME = Name, @TOPCOMPANYID = ID from TEAMFUNDRAISINGTEAM where ID = 
                (Select PARENTTEAMID from TEAMFUNDRAISINGTEAM where ID= @PARENTTEAMID)
    End  
 END  

 select   
  E.ID as PRIORYEAREVENTID,  
  E.NAME,  
  @TEAMNAME AS PRIORYEARTEAM,  
  @ISTEAMCAPTAIN AS ISTEAMCAPTAIN  ,
  @PARENTTEAMNAME AS PARENTGROUPNAME,
  @TEAMID as PRIORYEARTEAMID,
  @PARENTTEAMID as PRIORYEARPARENTTEAMID,
  @PARENTTEAMTYPECODE as PARENTTYPECODE,
  @TOPCOMPANYNAME As PRIORTOPCOMPANYNAME,
  @TOPCOMPANYID AS PRIORTOPCOMPANYID
 from dbo.EVENT E (NOLOCK)
 where E.ID = @PRIORYEAREVENTID