USP_DATALIST_FAFMERGEFIELDSPREPROCESSDATA

CREATE procedure dbo.USP_DATALIST_FAFMERGEFIELDSPREPROCESSDATA
  (
      @EVENTID uniqueidentifier = null,
      @CONSTITUENTID uniqueidentifier = null,
      @REGISTRANTID uniqueidentifier = null
  )
  AS 
  SET NOCOUNT ON;
select 
HASREVENUE=(case when R.ID IS NULL THEN 0 ELSE 1 END)
,HASTRIBUTE=(case when RT.TRIBUTEID IS NULL THEN 0 ELSE 1 END)
,HASINDIVIDUAL=(CASE WHEN RGE.REGISTRANTID IS NULL THEN 0 ELSE 1 END)
,HASHOUSEHOLD=(CASE WHEN TE.TYPECODE=3 THEN 1 ELSE 0 END)
,HASTEAM=(CASE WHEN TE.TYPECODE=1 THEN 1 ELSE 0 END)
,HASCOMPANY=(CASE WHEN TE.TYPECODE=2 THEN 1 WHEN TFT.PARENTTEAMID IS NOT null then 1 ELSE 0 END)
,HasBillingAddress = case when AT.DESCRIPTION = 'billing' then 1 else 0 END
,HasShippingAddress = case when AT.DESCRIPTION = 'shipping' then 1 else 0 END
,C.SEQUENCEID
from dbo.REGISTRANT RG    
inner join [EVENT] EV ON EV.ID = RG.EVENTID  
inner  join dbo.CONSTITUENT C ON RG.CONSTITUENTID = C.ID
--Revenue information

left outer join dbo.REVENUE R on R.CONSTITUENTID=RG.CONSTITUENTID
--tribute

LEFT outer join dbo.REVENUETRIBUTE RT ON RT.REVENUEID=R.ID 
--Individual

left outer join dbo.REGISTRANTEXTENSION RGE ON RGE.REGISTRANTID=RG.ID  
--Household,Team

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
--address

left join dbo.ADDRESS ADDR ON C.ID=ADDR.CONSTITUENTID 
left join dbo.ADDRESSTYPECODE AT ON ADDR.ADDRESSTYPECODEID=AT.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)