UFN_EVENTS_GETCONFLICTINGEVENTS

            CREATE function dbo.UFN_EVENTS_GETCONFLICTINGEVENTS
            (
                @ID uniqueidentifier
            )
            returns table
            as return 
            (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
            inner join dbo.PROGRAM on (EVENT.PROGRAMID = PROGRAM.ID or EVENT.PROGRAMID is null)
            where
                PROGRAM.ISACTIVE = 1 and
                (
                    (((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) >= STARTTIME) and ((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) < ENDTIME)) or
                    (((select ENDTIME from dbo.EVENTCONFLICT where ID=@ID) > STARTTIME) and ((select ENDTIME from dbo.EVENTCONFLICT where ID=@ID) <= ENDTIME)) or
                    (((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) > STARTTIME) and ((select ENDTIME from dbo.EVENTCONFLICT where ID=@ID) <= ENDTIME)) or 
                    (((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) <= STARTTIME) and ((select ENDTIME from dbo.EVENTCONFLICT where ID=@ID) >= ENDTIME))
                )  
                and STARTDATE=ENDDATE
                and STARTDATE in (select STARTDATE from dbo.EVENTCONFLICT where EVENTCONFLICT.ID=@ID)
                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 (EVENT.ID in (select EVENT.ID from dbo.EVENT left join dbo.PROGRAM on EVENT.PROGRAMID=PROGRAM.ID and PROGRAM.ISACTIVE=1))

            union 

            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
                inner join dbo.PROGRAM on (EVENT.PROGRAMID = PROGRAM.ID or EVENT.PROGRAMID is null)
                where 
                    PROGRAM.ISACTIVE = 1 and
                    (
                        (((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) <= STARTTIME) and ((select ENDTIME from dbo.EVENTCONFLICT where ID=@ID) > STARTTIME)) or 
                        ((select STARTTIME from dbo.EVENTCONFLICT where ID=@ID) >= STARTTIME)
                    )
                and STARTDATE < ENDDATE 
                and STARTDATE in (select STARTDATE from dbo.EVENTCONFLICT where EVENTCONFLICT.ID=@ID)
                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 (EVENT.ID in (select EVENT.ID from dbo.EVENT left join dbo.PROGRAM on EVENT.PROGRAMID=PROGRAM.ID and PROGRAM.ISACTIVE=1))
            )