USP_DATALIST_TEAMEXTENSIONSTATUS

create procedure dbo.USP_DATALIST_TEAMEXTENSIONSTATUS(
      @EVENTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @REGISTRANTID uniqueidentifier = null
)
AS

set nocount on;

select 
FundraisingGroup=(case TE.TYPECODE when 1 then 'Team' when 2 then 'Company' when 3 then 'Household' else null end)
,STATUSCODE=isnull(TE.STATUSCODE,0)
,TE.STATUS
from 
dbo.REGISTRANT RG    
inner join [EVENT] EV ON EV.ID = RG.EVENTID  
inner join dbo.CONSTITUENT C ON RG.CONSTITUENTID=C.ID 
left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EV.APPEALID 
left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID 
left outer join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
left outer join dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
WHERE (RG.ID = @REGISTRANTID OR @REGISTRANTID is null) 
and (RG.CONSTITUENTID = @CONSTITUENTID or @CONSTITUENTID is null)
and (EV.id = @EVENTID OR @EVENTID is null)