UFN_EVENT_GETRELATEDEVENTSCHEDULES

CREATE function dbo.UFN_EVENT_GETRELATEDEVENTSCHEDULES
(
    @EVENTID uniqueidentifier
)
returns table
as
return
(
    --  Since the client may need a schedule from any event within this event's hierarchy (on edit), we just grab all the schedules from the entire hierarchy

    select
        EVENT.ID,
        EVENT.NAME,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        EVENT.ENDDATE,
        EVENT.ENDTIME
    from
        dbo.EVENT
    where
        EVENT.ID = @EVENTID

    union

    select
        RELATED.ID,
        RELATED.NAME,
        RELATED.STARTDATE,
        RELATED.STARTTIME,
        RELATED.ENDDATE,
        RELATED.ENDTIME
    from
        dbo.EVENT 
    join
        dbo.EVENT RELATED 
    on 
        (RELATED.MAINEVENTID = EVENT.ID and EVENT.MAINEVENTID is null)
    or
        (EVENT.MAINEVENTID is not null and (RELATED.MAINEVENTID = EVENT.MAINEVENTID or RELATED.ID = EVENT.MAINEVENTID))
    where
        EVENT.ID = @EVENTID

)