UFN_EVENT_RELATEDEVENTPAIRSWITHSCHEDULECONFLICT

create function dbo.UFN_EVENT_RELATEDEVENTPAIRSWITHSCHEDULECONFLICT
(
    @EVENTID uniqueidentifier
) returns @EVENTPAIRS table(EVENTID1 uniqueidentifier, EVENTID2 uniqueidentifier) 
as begin
    declare @mainEventId uniqueidentifier
    select @mainEventId = coalesce(MAINEVENTID, @EVENTID) from dbo.EVENT where ID = @EVENTID
    declare @isMainEvent bit = Case @mainEventId when @EVENTID Then 1 Else 0 End
    declare @events table(ID uniqueidentifier, START datetime, [END] datetime, ROWNUM int)
    insert into @events
        select ID, STARTDATETIME, ENDDATETIME, ROW_NUMBER() OVER(order by STARTDATETIME, ENDDATETIME) from dbo.[EVENT]
        where MAINEVENTID = @mainEventId
        and STARTTIME <> '' AND ENDTIME <> ''
    insert into @EVENTPAIRS
        select a.ID, b.ID 
        from @events a
        inner join @events b 
        on a.ROWNUM + 1 = b.ROWNUM
        where a.[END] > b.START
        and ((@isMainEvent = 1) OR ((@isMainEvent = 0) and ((a.ID = @EVENTID) or (b.ID = @EVENTID))))
    return 
end