UFN_CONSTITUENT_HASMINIMUMTEAMLEADER

create function dbo.UFN_CONSTITUENT_HASMINIMUMTEAMLEADER(@SOURCEID uniqueidentifier, @TARGETID uniqueidentifier)
returns bit
with execute as caller
as begin
    declare @EVENTID uniqueidentifier

  if exists(select top(1) a.TEAMFUNDRAISINGTEAMID, b.TEAMFUNDRAISINGTEAMID, txa.EVENTID 
              from dbo.TEAMFUNDRAISINGTEAMCAPTAIN a
              cross apply dbo.TEAMFUNDRAISINGTEAMCAPTAIN b 
              join dbo.TEAMEXTENSION txa on a.TEAMFUNDRAISINGTEAMID = txa.TEAMFUNDRAISINGTEAMID
              join dbo.TEAMEXTENSION txb on b.TEAMFUNDRAISINGTEAMID = txb.TEAMFUNDRAISINGTEAMID
              where a.CONSTITUENTID = @SOURCEID
                  and b.CONSTITUENTID = @TARGETID
                  and txa.EVENTID = txb.EVENTID )
  begin

      ;With FAFTeamCaptains as 
      (
          select a.TEAMFUNDRAISINGTEAMID as SRCTEAMID, b.TEAMFUNDRAISINGTEAMID as TRGTEAMID, txa.EVENTID 
          from dbo.TEAMFUNDRAISINGTEAMCAPTAIN a
          cross apply dbo.TEAMFUNDRAISINGTEAMCAPTAIN b 
          join dbo.TEAMEXTENSION txa on a.TEAMFUNDRAISINGTEAMID = txa.TEAMFUNDRAISINGTEAMID
          join dbo.TEAMEXTENSION txb on b.TEAMFUNDRAISINGTEAMID = txb.TEAMFUNDRAISINGTEAMID
          where a.CONSTITUENTID = @SOURCEID
              and b.CONSTITUENTID = @TARGETID
              and txa.EVENTID = txb.EVENTID
      ),
      Counts as (
          select COUNT(TFC.ID) Cnt, EVENTID from FAFTeamCaptains
          join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFC on SRCTEAMID = TFC.TEAMFUNDRAISINGTEAMID
          group by EVENTID 
      )
      select top 1 @EVENTID=EVENTID FROM Counts WHERE Cnt < 2

      if @EVENTID is not null
      return 1
    end    

  return 0

end