SELECT G.GameID, GameDate, G.GameTime , G.HomeTeamID, G.AwayTeamID,
Home.TeamName as Home, Away.TeamName as Away, Complete, G.HomeTeamResult,
G.AwayTeamResult, G.HomeTeamScore, G.AwayTeamScore, Description, G.AMorPM,
SH.school_name as HomeSchool , SA.school_name as AwaySchool,
Home.short_name as HShort, Away.short_name as AShort, L.short_name,
CASE
WHEN AMorPM = 'PM' and GameTime < 12000
THEN GameTime +120005
ELSE GameTime +5
END as 'NEWTIME', L.LeagueName, sport_name, Away.school_id_fk as AwaySchools,
Home.school_id_fk as HomeSchools ,
Home.logo as homelogo,
Away.logo as awaylogo,
S.sport_icon,
RTA.ResultName as AwayResult,
RTH.ResultName as HomeResult,
Home.*
From Games G
Left Join Teams Home on Home.TeamID = HomeTeamID
Left Join Teams Away on Away.TeamID = AwayTeamID
Left Join Schools SH on SH.school_id = Home.school_id_fk
Left Join Schools SA on SA.school_id = Away.school_id_fk
LEFT JOIN Leagues L on L.LeagueID = G.LeagueID
Left Join Sports S on S.sport_id = L.sport_id_fk
LEFT JOIN ResultTypes RTH on HomeTeamResult = RTH.ResultTypeID
LEFT JOIN ResultTypes RTA on AwayTeamResult = RTA.ResultTypeID
Where G.is_deleted = 0 AND
(Home.school_id_fk = 36 or Away.school_id_fk = 36)
AND GameDate > '10/1/2014'
ORDER BY GameDate, NEWTIME ASC, GameTime ASC