spGetUsersAthleticEvents

CREATE PROCEDURE [dbo].[spGetUsersAthleticEvents] (@EEID INT, @SchedStartDate DATETIME, @SchedEndDate DATETIME) AS
SET NOCOUNT ON

    DECLARE @Teams TABLE (
        TeamID INT
    )

    --If record is a player, get his teams

    INSERT INTO @Teams (TeamID)
    SELECT ar.TeamID
    FROM dbo.ATHLETICS_ROSTERS ar
    WHERE ar.PlayerEEID = @EEID

    --If record is a coach, get his teams

    INSERT INTO @Teams (TeamID)
    SELECT ac.TeamID
    FROM dbo.ATHLETICS_EECOACHES ac
    WHERE ac.CoachEEID = @EEID


    SELECT DISTINCT 
        (Case 
            WHEN COALESCE(cer.BeginDate, ce.BeginDate) < @SchedStartDate
                THEN dbo.fnFormatDateTime(@SchedStartDate, 'YYYY-MM-DD')
            ELSE 
                dbo.fnFormatDateTime(COALESCE(cer.BeginDate, ce.BeginDate), 'YYYY-MM-DD')
            END) StartDate,
        COALESCE(cer.BeginDate, ce.BeginDate) StartDateTime,
        COALESCE(cer.EndDate, ce.EndDate) EndDateTime,
        ce.AllDayEvent AllDay,
        ce.Cancelled Cancelled,
        cte1.Description Sport,
        [EventCategory] =    
            Case Category 
                When '' Then ''
                Else (SELECT dbo.fnGetSiteCodeTableEntryDescription(1, Category))
            End,             al.Name Location
    FROM @Teams t
        INNER JOIN dbo.ATHLETICS_TEAMS at
            ON t.TeamID = at.ID
        INNER JOIN dbo.ATHLETICS_EVENTS ae
            ON t.TeamID = ae.TeamID
        INNER JOIN dbo.CalendarEvent ce
            ON ae.ID = ce.AthleticEventID
        LEFT JOIN dbo.CalendarEvtRecur cer
            ON ce.ID = cer.EventID
        LEFT JOIN ATHLETICS_LOCATIONS al
            ON ae.LocationID = al.ID
        LEFT JOIN dbo.sitecodetableentries cte1
            ON at.Sport = cte1.EntryGuid
    WHERE 
        (        --An event whose start date falls between the schedule date bounds

                (COALESCE(cer.BeginDate, ce.BeginDate) >= @SchedStartDate
                AND COALESCE(cer.BeginDate, ce.BeginDate) <= @SchedEndDate)
            OR    --An event whose end date falls between the schedule date bounds

                (COALESCE(cer.EndDate, ce.EndDate) >= @SchedStartDate
                AND COALESCE(cer.EndDate, ce.EndDate) <= @SchedEndDate)
            OR  --An event that starts before the schedule start date and ends after the schedule end date

                (COALESCE(cer.BeginDate, ce.BeginDate) <= @SchedStartDate
                AND COALESCE(cer.EndDate, ce.EndDate) >= @SchedEndDate)
        )
        AND ce.Deleted = 0
    ORDER BY StartDate, AllDay Desc, StartDateTime, Sport, EventCategory, EndDateTime