UFN_EVENTCONFLICT_GETCONFLICTINGEVENTS

            CREATE function dbo.UFN_EVENTCONFLICT_GETCONFLICTINGEVENTS
            (
                @ID uniqueidentifier
            )
            returns @EVENTS table
            (
                ID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                NAME nvarchar(50),
                STARTDATE datetime,
                ENDDATE datetime,
                CONFLICTINGEVENTTIMESPAN nvarchar(30),
                LOCATIONS nvarchar(500)
            )
            begin

                declare @CONFLICTSTARTDATE date;
                declare @CONFLICTSTARTDATETIME datetime;
                declare @CONFLICTSTARTTIME dbo.UDT_HOURMINUTE;
                declare @CONFLICTENDDATE date;
                declare @CONFLICTENDDATETIME datetime;
                declare @CONFLICTENDTIME dbo.UDT_HOURMINUTE;

                select
                    @CONFLICTSTARTDATE = STARTDATE,
                    @CONFLICTSTARTTIME = STARTTIME,
                    @CONFLICTSTARTDATETIME = STARTDATETIME,
                    @CONFLICTENDDATE = ENDDATE,
                    @CONFLICTENDTIME = ENDTIME,
                    @CONFLICTENDDATETIME = ENDDATETIME
                from dbo.EVENTCONFLICT
                where EVENTCONFLICT.ID = @ID

                insert into @EVENTS
                select
                    EVENT.ID,
                    EVENT.PROGRAMID,
                    EVENT.NAME,
                    EVENT.STARTDATE,
                    EVENT.ENDDATE,
                    dbo.UFN_GETEVENT_TIMESPAN(EVENT.ID) as CONFLICTINGEVENTTIMESPAN,
                    dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONS
                from
                    dbo.EVENT
                left join dbo.PROGRAM on 
                    EVENT.PROGRAMID = PROGRAM.ID
                where
                    (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
                    (STARTDATE = @CONFLICTSTARTDATE or @CONFLICTSTARTDATE between STARTDATE and ENDDATE) and 
                    (
                        EVENT.ID in 
                            (select A.EVENTID from dbo.PROGRAMEVENTLOCATION A
                            inner join dbo.PROGRAMEVENTLOCATION B 
                            on A.EVENTLOCATIONID=B.EVENTLOCATIONID
                            and B.EVENTCONFLICTID=@ID)
                        or 
                        EVENTLOCATIONID in 
                            (select EVENTLOCATIONID from dbo.PROGRAMEVENTLOCATION where EVENTCONFLICTID=@ID)
                    )
                    and
                    (
                        (
                            STARTDATE = ENDDATE and
                            (
                                (@CONFLICTSTARTTIME >= STARTTIME and @CONFLICTSTARTTIME < ENDTIME) 
                                or
                                (@CONFLICTENDTIME > STARTTIME and @CONFLICTENDTIME <= ENDTIME) 
                                or
                                (@CONFLICTSTARTTIME > STARTTIME and @CONFLICTENDTIME <= ENDTIME) 
                                or 
                                (@CONFLICTSTARTTIME <= STARTTIME and @CONFLICTENDTIME >= ENDTIME)
                            )
                        )
                        or
                        (
                            STARTDATE < ENDDATE and
                            (
                                (@CONFLICTSTARTDATETIME <= STARTDATETIME and @CONFLICTENDDATETIME > STARTDATETIME) 
                                or
                                (@CONFLICTSTARTDATETIME >= STARTDATETIME and @CONFLICTSTARTDATETIME < ENDDATETIME)
                            ) 
                        )
                    )

                order by EVENT.STARTDATE, EVENT.STARTTIME

                return;
            end