UFN_EVENTCONFLICT_EVENTSINCONFLICT

      CREATE function dbo.UFN_EVENTCONFLICT_EVENTSINCONFLICT
      (
        @EVENTCONFLICTID uniqueidentifier
      )
      returns int
      as begin
        -- get the information we need about the conflict

        declare @START datetime;
        declare @END datetime;
        declare @LOCATIONS xml;
        declare @RESOURCES xml;
        declare @STAFFRESOURCES xml;

        select @START = EVENTCONFLICT.STARTDATETIME,
               @END   = EVENTCONFLICT.ENDDATETIME,
               @LOCATIONS = dbo.UFN_EVENTCONFLICT_GETLOCATIONS_TOITEMLISTXML(@EVENTCONFLICTID),
               @RESOURCES = dbo.UFN_EVENTCONFLICT_GETRESOURCES_TOITEMLISTXML(@EVENTCONFLICTID),
               @STAFFRESOURCES = dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCES_TOITEMLISTXML(@EVENTCONFLICTID)
        from dbo.EVENTCONFLICT
        where EVENTCONFLICT.ID = @EVENTCONFLICTID

        -- First straight event conflicts

        declare @EVENTCONFLICTCOUNT int = 0;

        select @EVENTCONFLICTCOUNT = count(distinct EVENT.ID)
        from dbo.EVENT 
          left outer join dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTS(@START, @END, @LOCATIONS, null, null, 1, 0) as LOCATIONCONFLICT
            on EVENT.ID = LOCATIONCONFLICT.EVENTID
          left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTS(@START, @END, @RESOURCES, null, null, 1, 0, 0) as RESOURCECONFLICT
            on EVENT.ID = RESOURCECONFLICT.EVENTID
          left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTS(@START, @END, @STAFFRESOURCES, null, null, 1, 0, 0) as STAFFRESOURCECONFLICT
            on EVENT.ID = STAFFRESOURCECONFLICT.EVENTID
        where (not LOCATIONCONFLICT.EVENTID is null) or (not RESOURCECONFLICT.EVENTID is null) or (not STAFFRESOURCECONFLICT.EVENTID is null)

        -- Second itinerary item conflicts

        declare @ITINERARYITEMCONFLICTCOUNT int = 0;

        select @ITINERARYITEMCONFLICTCOUNT = count(distinct ITINERARYITEM.ID)
        from dbo.ITINERARYITEM 
          left outer join dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTS(@START, @END, @LOCATIONS, null, null, 0, 1) as LOCATIONCONFLICT
            on ITINERARYITEM.ID = LOCATIONCONFLICT.ITINERARYITEMID
          left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTS(@START, @END, @RESOURCES, null, null, 0, 1, 0) as RESOURCECONFLICT
            on ITINERARYITEM.ID = RESOURCECONFLICT.ITINERARYITEMID
          left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTS(@START, @END, @STAFFRESOURCES, null, null, 0, 1, 0) as STAFFRESOURCECONFLICT
            on ITINERARYITEM.ID = STAFFRESOURCECONFLICT.ITINERARYITEMID
        where (not LOCATIONCONFLICT.EVENTID is null) or (not RESOURCECONFLICT.EVENTID is null) or (not STAFFRESOURCECONFLICT.EVENTID is null)

        -- Second itinerary conflicts

        declare @ITINERARYCONFLICTCOUNT int = 0;

        select @ITINERARYCONFLICTCOUNT = count(distinct ITINERARY.ID)
        from dbo.ITINERARY
            left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTS(@START, @END, @RESOURCES, null, null, 0, 0, 1) as RESOURCECONFLICT
                on ITINERARY.ID = RESOURCECONFLICT.ITINERARYID
            left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTS(@START, @END, @STAFFRESOURCES, null, null, 0, 0, 1) as STAFFRESOURCECONFLICT
                on ITINERARY.ID = STAFFRESOURCECONFLICT.ITINERARYID
        where (not RESOURCECONFLICT.EVENTID is null) or (not STAFFRESOURCECONFLICT.EVENTID is null)

        return @EVENTCONFLICTCOUNT + @ITINERARYITEMCONFLICTCOUNT + @ITINERARYCONFLICTCOUNT;
      end